SQL Constraints: Primary Key Constraint

The primary key constraint in SQL is used to uniquely identify each record in a table. It ensures that no duplicate values are inserted into the primary key field, thereby enforcing data integrity.

Syntax

CREATE TABLE table_name (
   column1 datatype constraint_name PRIMARY KEY,
   column2 datatype,
   column3 datatype,
   ...
);

Example

Let's consider an example of a company database where we need to create a table for employees. Here is the SQL code for creating the employees table with primary key constraint on the emp_id column:

CREATE TABLE employees (
   emp_id INT PRIMARY KEY,
   first_name VARCHAR(50) NOT NULL,
   last_name VARCHAR(50) NOT NULL,
   email VARCHAR(100) UNIQUE,
   hire_date DATE NOT NULL
);

Output

Once the employees table is created, it will look like this:

emp_id	first_name	last_name	email	hire_date

Explanation

The primary key constraint is applied to the emp_id column of the employees table. The data type for the emp_id column is INT. The PRIMARY KEY keyword is used to specify the primary key constraint, and the constraint name is optional (if not provided, a system generated name is used).

Use

The primary key constraint is used to ensure data integrity in a table by enforcing unique values for the specified column(s). The primary key constraint is also used as a reference in other tables to establish relationships between tables.

Important Points

• A table can have only one primary key constraint.
• The primary key column(s) must contain unique and non-null values.
• If a primary key field is empty or null, it violates the primary key constraint.
• You cannot update or delete a record that has a foreign key constraint pointing to it.

Summary

The primary key constraint is essential for maintaining data integrity and establishing relationships between tables in a database. It ensures each record in a table is uniquely identified and enforces the rule that no duplicate values are inserted into the primary key field. Remember, a table can only have one primary key, and the primary key column(s) must contain unique and non-null values.