At times, ensuring all rows in a table are unique is necessary. For instance, storing information about conference participants—name, email, date of birth, and city—requires avoiding duplicate registrations. In this scenario, a unique combination of data is needed for each participant. While some individuals may share the same name, their email addresses will certainly differ, making it suitable to use this field as a unique key to prevent duplicates. This unique key is commonly referred to as a primary key.
PRIMARY KEY constraint
The PRIMARY KEY constraint specifies a set of columns with values that can help identify any table record.
This constraint can be specified in creating a table named chefs with the columns chef_id INT, first_name VARCHAR(20), and last_name VARCHAR(20). Assuming that all chefs have individual identifiers, make the chef_id column the primary key:
The PRIMARY KEY constraint means that the chef_id column must contain unique values for each chef. No two chefs can have the same chef_id.
Since the primary key has to identify each table row, it must be unique and cannot be null.
Another important thing is that a table can have one and only one primary key, but it is allowed to include multiple columns in it.
Consider the employees table with the columns department_id, employee_id, and name. Assume that it's possible to have two employees with identical identifiers across different departments, but it is impossible to have several employees with identical id's in a single department. So, there can be tuples (42, 15, 'Ann Brown') and (43, 15, 'Bob Freud') in the table, but it's not possible to add a tuple (42, 15, 'John Smith') to that table since there already is an Ann Brown with an id '42'.
In this case, define a named PRIMARY KEY constraint on multiple columns when creating the employees table:
The syntax from the query above can also be used to create a named PRIMARY KEY constraint on one column.
Level up your tech skills and advance your career
• Wide range of SQL and Databases tracks
• Study at your own pace with your personal study plan
• Focus on practice and real-world experience