SQL Server Primary Key
SQL Server Primary key is a single field or combination of fields that is used to uniquely define a record. Any field of a primary key cannot contain a null value. A table can have only one primary key.
You can define a primary key either in a CREATE TABLE statement or an ALTER TABLE statement.
Create Primary Key Using CREATE TABLE Statement
Syntax:
Or
Example:
Create a table “cricketers” where “cricketer_id” is a primary key.
Output:
You can verify that created table by using SELECT command:
Output:
Here cricketer_id is the primary key.
You can also create a table with primary key by using the second syntax:
Example:
Output:
You can verify that created table by using SELECT command:
Output:
Create a primary key in SQL Server where the primary key is more than one field:
Example:
Output:
You can verify that created table by using SELECT command:
Output:
Here, last_name and first_name both is primary key.
Create Primary Key Using ALTER TABLE statement
You can use an ALTER TABLE statement to create a primary key only on the column or columns that are already defined as NOT NULL.
If a column of a table has NULL values, you cannot add a primary key without dropping and recreating the table.
Syntax:
Example:
Create a primary key “cricketer_id” in the table “cricketers2”.
Output:
It will create a primary key “cricketer_id” in the “cricketers2” table.