SQL Primary Key

SQL > Constraint > Primary Key

A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has no meaning other than being an identifier of the record). How to set primary keys is an important factor in database design, as the choices of primary key can have significant impact on the performance, usability, and the extensibility of the entire database.

A primary key can consist of one or more columns on a table. When multiple columns are used as a primary key, they are called a composite key.

In designing the primary key, we want to make sure we use as few columns as possible. This is for both storage and performance reasons. Since primary key information requires additional storage in a relational database, the more columns a primary key includes, the more storage it requires. In terms of performance, fewer columns means that the database can process through the primary key faster since there is less data, and it also means that table joins will be faster since primary keys, together with foreign keys, are often used as the joining condition.

A primary key cannot be NULL, as it does not make sense to use the NULL value to uniquely identify a record. Therefore, the column that is set as a primary key or as part of a primary key cannot be NULL.

Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).

Below are examples for specifying a primary key when creating a table. Please note when primary key is specified upon table creation, the column(s) that are set to be the primary key are automatically set to NOT NULL.

Primary Key On One Column

MySQL:

CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));

Oracle:

CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

SQL Server:

CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name nvarchar(30),
First_Name nvarchar(30));

In the examples above, SID column is specified as the primary key.

Below is an example of specifying the primary key on a single column using ALTER TABLE. The same SQL statement can be used in MySQL, Oracle, and SQL Server.

ALTER TABLE Customer ADD PRIMARY KEY (SID);

Note: Before using the ALTER TABLE command to add a primary key, you'll need to make sure that the field is defined as 'NOT NULL' - in other words, NULL cannot be an accepted value for that field.

Primary Key On Multiple Columns

In the examples below, the composite primary key consists of two columns, Birth_Date and Social_Security_Number.

MySQL:

CREATE TABLE Customer_Composite_Key
(Last_Name varchar(30),
First_Name varchar(30),
Birth_Date datetime,
Social_Security_Number integer,
PRIMARY KEY (Birth_Date, Social_Security_Number));

Oracle:

CREATE TABLE Customer_Composite_Key
(Last_Name varchar(30),
First_Name varchar(30),
Birth_Date date,
Social_Security_Number integer,
PRIMARY KEY (Birth_Date, Social_Security_Number));

SQL Server:

CREATE TABLE Customer_Composite_Key
(Last_Name narchar(30),
First_Name narchar(30),
Birth_Date datetime,
Social_Security_Number integer,
PRIMARY KEY (Birth_Date, Social_Security_Number));

Below is an example of using ALTER TABLE to add a primary key that consists of two columns. The command is the same in MySQL, Oracle, and SQL Server.

ALTER TABLE Customer_Composite_Key ADD PRIMARY KEY (Birth_Date, Social_Security_Number);

Next: SQL FOREIGN KEY

This page was last updated on October 11, 2024.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.