Tables without primary keys are largely unusable in a relational database because they cannot be joined to. A primary key should be specified at
table creation to guarantee that all its records have primary key values.
Noncompliant code example
CREATE TABLE employee
(
employee_id INTEGER NOT NULL,
first_name VARCHAR(42) NOT NULL,
last_name VARCHAR(42) NOT NULL
);
Compliant solution
CREATE TABLE employee
(
employee_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(42) NOT NULL,
last_name VARCHAR(42) NOT NULL
);
CREATE TABLE employee
(
employee_id INTEGER NOT NULL,
first_name VARCHAR(42) NOT NULL,
last_name VARCHAR(42) NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY (employee_id)
);
Exceptions
No issue is reported on temporary tables as they are often used to manipulate data and do not always require a primary key.
Example:
CREATE TABLE #EmployeeName (last_name VARCHAR(42) NOT NULL); -- Compliant
