SQL PRIMARY KEY

PRIMARY KEY

The SQL PRIMARY KEY is a column in a table which must contain a unique value which can be used to identify each and every row of a table uniquely.

However, SQL supports primary keys directly with the PRIMARY KEY constraint.

Functionally, it is the same as the UNIQUE constraint, except that only one PRIMARY KEY can be defined for a given table. PRIMARY KEY's will not allow NULL values.

A primary key is used to identify each row identically in a table. It may be a part of the actual record itself.

The SQL PRIMARY KEY can be made up by one or more fields on a table and when it happens, they are called a composite key.

Primary keys can be specified at the time of CREATING TABLE or the time of changing the structure of the existing table using ALTER TABLE statement.

This constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. This constraint ensures that the specific column or combination of two or more columns for a table have a unique identity which helps to find a particular record in a table more easily and quickly.

Syntax:

CREATE TABLE <table_name>
column1    data_type[(size)]  NOT NULL  PRIMARY KEY,
column2    data_type[(size)],
...);

Parameters:

Good practice for primary keys in tables

• Primary keys should be as small as necessary. Prefer a numeric type because numeric types are stored in a much more compact format than character formats.
• Primary keys should never change.
• Do not use passport number, social security number, or employee contract number as "primary key" as these "primary key" can change for real world situations.

Example:

Suppose, we are going to create a table named 'agent1'. It contains the columns and data types which shown bellow. For each row of 'agent1' table, it is required to identify each agent with a unique code, because the name of two or more agents of a city of a country may be same.

So, it is not a good choice to create PRIMARY KEY on 'agent_name'. The 'agent_code' could be the only and exclusive choice for a PRIMARY KEY for this table.

While creating a table you can include a primary key using column-level primary-key constraint or table-level constraint. Here are two examples on the said table :

Column-level primary-key constraint :

SQL Code:

-- Creating a table named 'agent1'
CREATE TABLE agent1 (
    -- Column: agent_code with a data type CHAR(6), NOT NULL, and PRIMARY KEY
    agent_code CHAR(6) NOT NULL PRIMARY KEY,
    
    -- Column: agent_name with a data type CHAR(40) and NOT NULL constraint
    agent_name CHAR(40) NOT NULL,
    
    -- Column: working_area with a data type CHAR(35)
    working_area CHAR(35),
    
    -- Column: commission with a decimal data type (10 digits, 2 decimal places)
    commission DECIMAL(10, 2),
    
    -- Column: phone_no with a data type CHAR(17)
    phone_no CHAR(17)
);

Explanation:

• Creating a table named 'agent1': This is a comment line indicating the purpose of the SQL code, which is to create a table named 'agent1'.
• CREATE TABLE agent1 (: This line initiates the creation of a new table named 'agent1'.
• agent_code CHAR(6) NOT NULL PRIMARY KEY,: This line defines a column named 'agent_code' with a data type CHAR(6). CHAR is a fixed-length character data type, and in this case, it allows up to 6 characters. The column is specified as NOT NULL, meaning it must have a value for each row, and it is designated as the PRIMARY KEY for the table, ensuring uniqueness of values and efficient indexing.
• agent_name CHAR(40) NOT NULL,: This line defines a column named 'agent_name' with a data type CHAR(40). It's similar to 'agent_code', but it allows up to 40 characters. The column is also specified as NOT NULL.
• working_area CHAR(35),: This line defines a column named 'working_area' with a data type CHAR(35), allowing up to 35 characters. No constraints are specified, so NULL values are allowed for this column.
• commission DECIMAL(10, 2),: This line defines a column named 'commission' with a DECIMAL data type. DECIMAL is a numeric data type that can store fixed-point numbers. Here, it's specified to have 10 digits in total, with 2 of those being reserved for decimal places.
• phone_no CHAR(17): This line defines a column named 'phone_no' with a data type CHAR(17). The column does not have any constraints specified, so NULL values are allowed.

Table-level primary-key constraint:

SQL Code:

-- Creating a table named 'agent1'
CREATE TABLE agent1 (
    -- Column: agent_code with a data type CHAR(6), NOT NULL
    agent_code CHAR(6) NOT NULL,
    
    -- Column: agent_name with a data type CHAR(40), NOT NULL
    agent_name CHAR(40) NOT NULL,
    
    -- Column: working_area with a data type CHAR(35)
    working_area CHAR(35),
    
    -- Column: commission with a decimal data type (10 digits, 2 decimal places)
    commission DECIMAL(10, 2),
    
    -- Column: phone_no with a data type CHAR(17)
    phone_no CHAR(17),
    
    -- Defining a primary key constraint named pk_agent_code on the agent_code column
    CONSTRAINT pk_agent_code PRIMARY KEY (agent_code)
);

Explanation:

• Creating a table named 'agent1': This is a comment line indicating the purpose of the SQL code, which is to create a table named 'agent1'.
• CREATE TABLE agent1 (: This line initiates the creation of a new table named 'agent1'.
• agent_code CHAR(6) NOT NULL,: This line defines a column named 'agent_code' with a data type CHAR(6). CHAR is a fixed-length character data type, and in this case, it allows up to 6 characters. The column is specified as NOT NULL, meaning it must have a value for each row.
• agent_name CHAR(40) NOT NULL,: This line defines a column named 'agent_name' with a data type CHAR(40). It's similar to 'agent_code', but it allows up to 40 characters. The column is also specified as NOT NULL.
• working_area CHAR(35),: This line defines a column named 'working_area' with a data type CHAR(35), allowing up to 35 characters. No constraints are specified, so NULL values are allowed for this column.
• commission DECIMAL(10, 2),: This line defines a column named 'commission' with a DECIMAL data type. DECIMAL is a numeric data type that can store fixed-point numbers. Here, it's specified to have 10 digits in total, with 2 of those being reserved for decimal places.
• phone_no CHAR(17),: This line defines a column named 'phone_no' with a data type CHAR(17). The column does not have any constraints specified, so NULL values are allowed.
• CONSTRAINT pk_agent_code PRIMARY KEY (agent_code): This line defines a primary key constraint named 'pk_agent_code' on the 'agent_code' column. It ensures that each value in the 'agent_code' column is unique and not null, effectively making it the primary key for the table.

SQL CREATE TABLE with PRIMARY KEY CONSTRAINT

SQL PRIMARY KEY CONSTRAINT is a combination of a NOT NULL constraint and a UNIQUE constraint. This constraint ensures that the specific column or combination of two or more columns for a table have an unique identity which helps to find a particular record in a table more easily and quickly.

Example :

The following example creates a table. Here is the field name and data types :

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: agent_code with a data type CHAR(6), NOT NULL, and PRIMARY KEY
    agent_code CHAR(6) NOT NULL PRIMARY KEY,
    
    -- Column: agent_name with a data type CHAR(40), NOT NULL
    agent_name CHAR(40) NOT NULL,
    
    -- Column: working_area with a data type CHAR(35)
    working_area CHAR(35),
    
    -- Column: commission with a decimal data type (10 digits, 2 decimal places)
    commission DECIMAL(10, 2),
    
    -- Column: phone_no with a data type CHAR(17)
    phone_no CHAR(17)
);

Explanation:

• Creating a table named 'mytest': This comment line indicates the purpose of the SQL code, which is to create a table named 'mytest'.
• CREATE TABLE mytest (: This line initiates the creation of a new table named 'mytest'.
• agent_code CHAR(6) NOT NULL PRIMARY KEY,: This line defines a column named 'agent_code' with a data type CHAR(6). CHAR is a fixed-length character data type, and in this case, it allows up to 6 characters. The column is specified as NOT NULL, meaning it must have a value for each row, and it is designated as the PRIMARY KEY for the table, ensuring uniqueness of values and efficient indexing.
• agent_name CHAR(40) NOT NULL,: This line defines a column named 'agent_name' with a data type CHAR(40). It's similar to 'agent_code', but it allows up to 40 characters. The column is also specified as NOT NULL.
• working_area CHAR(35),: This line defines a column named 'working_area' with a data type CHAR(35), allowing up to 35 characters. No constraints are specified, so NULL values are allowed for this column.
• commission DECIMAL(10, 2),: This line defines a column named 'commission' with a DECIMAL data type. DECIMAL is a numeric data type that can store fixed-point numbers. Here, it's specified to have 10 digits in total, with 2 of those being reserved for decimal places.
• phone_no CHAR(17): This line defines a column named 'phone_no' with a data type CHAR(17). The column does not have any constraints specified, so NULL values are allowed.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest; 

Output :

SQL CREATE TABLE with PRIMARY KEY and UNIQUE CONSTRAINT

The following example creates a table. Here is the field name and data types:

the following SQL statement can be used:

SQL Code:

-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: cust_code with a data type CHAR(6), NOT NULL, and PRIMARY KEY
    cust_code CHAR(6) NOT NULL PRIMARY KEY,
    
    -- Column: cust_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint
    cust_name CHAR(25) NOT NULL UNIQUE,
    
    -- Column: cust_city with a data type CHAR(25), NOT NULL
    cust_city CHAR(25) NOT NULL,
    
    -- Column: grade with a data type INTEGER
    grade INTEGER,
    
    -- Column: agent_code with a data type CHAR(6), NOT NULL
    agent_code CHAR(6) NOT NULL
);

Explanation:

• Creating a table named 'mytest': This comment line indicates the purpose of the SQL code, which is to create a table named 'mytest'.
• CREATE TABLE mytest (: This line initiates the creation of a new table named 'mytest'.
• cust_code CHAR(6) NOT NULL PRIMARY KEY,: This line defines a column named 'cust_code' with a data type CHAR(6). CHAR is a fixed-length character data type, and in this case, it allows up to 6 characters. The column is specified as NOT NULL, meaning it must have a value for each row, and it is designated as the PRIMARY KEY for the table, ensuring uniqueness of values and efficient indexing.
• cust_name CHAR(25) NOT NULL UNIQUE,: This line defines a column named 'cust_name' with a data type CHAR(25). It's similar to 'cust_code', but it allows up to 25 characters. The column is specified as NOT NULL, meaning it must have a value for each row, and it also has a UNIQUE constraint, ensuring that each value in this column is unique across all rows in the table.
• cust_city CHAR(25) NOT NULL,: This line defines a column named 'cust_city' with a data type CHAR(25), allowing up to 25 characters. No constraints are specified, so NULL values are not allowed for this column.
• grade INTEGER,: This line defines a column named 'grade' with a data type INTEGER. INTEGER is a numeric data type that can store whole numbers. No constraints are specified for this column, so NULL values are allowed.
• agent_code CHAR(6) NOT NULL: This line defines a column named 'agent_code' with a data type CHAR(6). The column is specified as NOT NULL, meaning it must have a value for each row.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output:

SQL CREATE TABLE with PRIMARY KEY CONSTRAINT on more columns

In the following topic, we are going to discuss the usage of SQL PRIMARY KEY CONSTRAINT along with the CREATE TABLE statement for two or more columns.

Example:

The following example creates a table. The table must contain a PRIMARY KEY with the combination of two columns 'cust_code' and 'cust_city'. Here is the field name and data types :

the following SQL statement can be used:

SQL Code:

-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: cust_code with a data type CHAR(6), NOT NULL
    cust_code CHAR(6) NOT NULL,
    
    -- Column: cust_name with a data type CHAR(25), NOT NULL
    cust_name CHAR(25) NOT NULL,
    
    -- Column: cust_city with a data type CHAR(25)
    cust_city CHAR(25),
    
    -- Column: grade with a data type INTEGER
    grade INTEGER,
    
    -- Column: agent_code with a data type CHAR(6), NOT NULL
    agent_code CHAR(6) NOT NULL,
    
    -- Defining a composite primary key on cust_code and cust_city columns
    PRIMARY KEY (cust_code, cust_city)
);

Explanation:

• Creating a table named 'mytest': This comment line indicates the purpose of the SQL code, which is to create a table named 'mytest'.
• CREATE TABLE mytest (: This line initiates the creation of a new table named 'mytest'.
• cust_code CHAR(6) NOT NULL,: This line defines a column named 'cust_code' with a data type CHAR(6). CHAR is a fixed-length character data type, and in this case, it allows up to 6 characters. The column is specified as NOT NULL, meaning it must have a value for each row.
• cust_name CHAR(25) NOT NULL,: This line defines a column named 'cust_name' with a data type CHAR(25). It's similar to 'cust_code', but it allows up to 25 characters. The column is also specified as NOT NULL.
• cust_city CHAR(25),: This line defines a column named 'cust_city' with a data type CHAR(25), allowing up to 25 characters. No constraints are specified, so NULL values are allowed for this column.
• grade INTEGER,: This line defines a column named 'grade' with a data type INTEGER. INTEGER is used for whole numbers.
• agent_code CHAR(6) NOT NULL,: This line defines a column named 'agent_code' with a data type CHAR(6). The column is specified as NOT NULL, meaning it must have a value for each row.
• PRIMARY KEY (cust_code, cust_city): This line defines a composite primary key constraint on the 'cust_code' and 'cust_city' columns. This means that the combination of values in these two columns must be unique for each row in the table, effectively making them jointly the primary key of the table.

To see the structure of the created table:

SQL Code:

DESCRIBE mytest;

Output:

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous:Create Table
Next: Foreign Key