SQL PRIMARY KEY
If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table.
When multiple columns are used as a primary key, it is known as composite primary key.
In designing the composite primary key, you should use as few columns as possible. It is good for storage and performance both, the more columns you use for primary key the more storage space you require.
Inn terms of performance, less data means the database can process faster.
Points to remember for primary key:
- Primary key enforces the entity integrity of the table.
- Primary key always has unique data.
- A primary key length cannot be exceeded than 900 bytes.
- A primary key cannot have null value.
- There can be no duplicate value for a primary key.
- A table can contain only one primary key constraint.
Main advantage of primary key:
The main advantage of this uniqueness is that we get fast access.
SQL primary key for one column:
The following SQL command creates a PRIMARY KEY on the "S_Id" column when the "students" table is created.
MySQL:
(
S_Id int NOT NULL,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
PRIMARY KEY (S_Id)
)
SQL Server, Oracle, MS Access:
(
S_Id int NOT NULL PRIMARY KEY,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
)
SQL primary key for multiple columns:
MySQL, SQL Server, Oracle, MS Access:
(
S_Id int NOT NULL,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
CONSTRAINT pk_StudentID PRIMARY KEY (S_Id, LastName)
)
Note:you should note that in the above example there is only one PRIMARY KEY (pk_StudentID). However it is made up of two columns (S_Id and LastName).
SQL primary key on ALTER TABLE
When table is already created and you want to create a PRIMARY KEY constraint on the "S_Id" column you should use the following SQL:
Primary key on one column:
ADD PRIMARY KEY (S_Id)
Primary key on multiple column:
ADD CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LastName)
When you use ALTER TABLE statement to add a primary key, the primary key columns must not contain NULL values (when the table was first created).
How to DROP a PRIMARY KEY constraint?
If you want to DROP (remove) a primary key constraint, you should use following syntax:
MySQL:
DROP PRIMARY KEY
SQL Server / Oracle / MS Access:
DROP CONSTRAINT pk_StudentID
