SQL primary key is a column in a table which uniquely identifies each row/record in a database table. Primary keys must have unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple columns.

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.

In terms of performance, less data means the database can process faster.

Points to remember for primary key:

  1. Primary key always has unique value.
  2. A primary key cannot have null value.
  3. There can be no duplicate value for a primary key.
  4. A table can contain only one primary key constraint.

SQL primary key for one column:

The following SQL command creates a PRIMARY KEY on the "UserId" column when the "Users" table is created.

 

CREATE TABLE Users  
( 
UserId 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:


The following SQL command creates a PRIMARY KEY on the "UserId","LastName" column when the "Users" table is created.

CREATE TABLE Users  
( 
UserId int NOT NULL, 
LastName varchar (255) NOT NULL, 
FirstName varchar (255), 
Address varchar (255), 
City varchar (255),
CONSTRAINT pk_UserID PRIMARY KEY (UserId, LastName)

Thanks.

0 comments


Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

You may also like