SQL Primary Key

What is a primary key?

In a database table, we have many values for each record. A primary key is a value or a group of values that uniquely describe each record. This simply means that a primary key will always have a unique value. This helps to reduce redundancy i.e. repetition of the same information. We will now understand a primary key with the help of an example. Below is a table named, "sailors":

sid	sname	age
22	Dustin	45
31	Lubber	55
15	Rustin	38
21	Akins	35
50	Fowler	49

The column "sid" is a primary key here. If we try to assign same values to "sid", it will give an error and the record won't be saved. It is important to remember that a table can have one and only one or a single set of primary key. A table cannot have two separate sets of primary keys.

Assigning primary key to a value

Now, suppose we want to create the table given above and assign "sid" as the primary key. The SQL for creating the above table is given below:

CREATE TABLE sailors
(sid int NOT NULL PRIMARY KEY,
sname varchar(255) NOT NULL,
age int NOT NULL);

The above table with the primary key, "sid" will be created. Now, suppose that the above table already exists but we have forgotton to define "sid" as the primary key. So, now we will assign "sid" as the primary key in the already exiting table, "sailors".

ALTER TABLE sailors
ADD PRIMARY KEY (sid);

Remember that while you are assigning primary key to a constraint, it's value should have been set to "NOT NULL" when it was created, else it will generate an error.