SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key.

When primary key is applied on a single attribute it is called Simple Key and when it is applied on two or more than two attributes then it is called as Composite Key.

A primary key is a special case of unique keys. The major difference is that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is. Thus, the values in unique key columns may or may not be NULL. It is a unique identifier, such as a driver license number, telephone number (including area code), or Vehicle identification number (VIN). A relational database must always have one and only one primary key. Primary keys typically appear as columns in relational database tables.

Use the following guidelines when selecting a primary key:

.• Whenever practical, use a column containing a sequence number. It is a simple way to satisfy all the other guidelines.

.• Minimize your use of composite primary keys. Although composite primary keys are allowed, they do not satisfy all of the other recommendations. For example, composite primary key values are long and cannot be assigned by sequence numbers,

.• Choose a column whose data values are unique, because the purpose of a primary key is to uniquely identify each row the table.

• Choose a column whose data values are never changed. A primary key value is only used to identify a row in the table, and its data should never be used for any other purpose. Therefore, primary key values should rarely or never be changed.

Choose a column that does not contain any nulls. A PRIMARY KEY constraint, by definition, does not allow any row to contain a null in any column that is part of the primary key.

Syntax: At Column level

(Column_name data type [Constraint <name>] PRIMARY KEY,

Syntax: At table Level:

[Constraint <name>] PRIMARY KEY (<column_name1><column_name2);

Query: The following SQL enforces the “P_Id” column to take unique and not null values.

SQL> CREATE TABLE Persons (P_Id number (6) PRIMARY KEY,LastName varchar2 (15),FirstName varcharZ (15),Address varchar2 (25),City varchar2 (15));

Table created.

Query: Table level

SQL> CREATE TABLE Persons(P_Id number (6),LastName varchar2 (15),FirstName varchar2 (15),Address varchar2 (25),City varchar2 (15), CONSTRAINT PK_Persons PRIMARY KEY(P_Id, FirstName));

Table created.