Home

Description

The rule that a primary key should be specified during table creation ensures that each record in a database table is uniquely identified. This is done by assigning a unique value, or combination of values, to each record. This makes it easier to locate and update individual records, as well as ensuring data integrity and preventing duplicate records. Specifying a primary key during table creation also helps to improve query performance by allowing the database engine to quickly identify and retrieve records.

Key Benefits

• Ensures Data Integrity: A primary key ensures that no two rows of data have the same value, thus ensuring data integrity.
• Improves Query Performance: By having a primary key, queries can be optimized to quickly find data in the table.
• Enables Referential Integrity: A primary key enables the use of foreign keys, which in turn enables referential integrity.

Non-compliant Code Example

CREATE TABLE Employee
(
  employee_id int NOT NULL,  --Non compliant code
  first_name VARCHAR(42) NOT NULL,
  last_name VARCHAR(42) NOT NULL
);

Compliant Code Example

CREATE TABLE Employee
(
  employee_id int IDENTITY(1,1) PRIMARY KEY,  --Compliant code
  first_name VARCHAR(42) NOT NULL,
  last_name VARCHAR(42) NOT NULL
);