A primary key is the column, or combination of columns, that identifies one row in a table. Every table has one primary key, and its values must be unique. In SQL, a primary key constraint also rejects NULL primary-key values, protecting the table from duplicate or unidentified rows. section 4.3 section 6.11
Give each product its own identity
A product table can use product_id as its primary key. Product names, prices, and manufacturers may describe a product, but product_id is the value used to identify its particular row. For example, a review with product_id 3 connects to the product row whose product_id is also 3. section 1.3 section 2.2
CREATE TABLE product ( product_id INT PRIMARY KEY, name TEXT NOT NULL, description TEXT NOT NULL, price DECIMAL(5, 2) NOT NULL, manufacturer TEXT NOT NULL );
This table definition places PRIMARY KEY directly on product_id, making that column the required unique identifier for each product row. section 1.10
Choose a primary key during design with these practical rules:
- Start with candidate keys: minimal attributes that can uniquely identify a row. A user’s username, email, and phone number can each be candidate keys when requirements say each must be unique. section 4.3
- Choose the best candidate key as the table’s one primary key. If no candidate key is a good fit, add a new attribute, often a numeric one, to serve as the primary key. section 4.3 section 4.9
- Use more than one column only when the identifier must be composite. For example, purchase_id together with code can identify a row in purchase_product. section 6.11
- Stop normalization work if a table lacks a primary key, identify it first, then continue toward BCNF. section 6.3
Primary keys do more than label rows. They enforce data integrity by preventing duplication and provide the values that foreign keys use to connect related tables. They also support normalized designs, where a table in BCNF has a primary key and avoids update or deletion problems caused by poorly organized data. section 4.3 section 6.2
Declare and name the constraint
For a single-column key, write PRIMARY KEY beside the column definition, such as email VARCHAR(320) PRIMARY KEY. When a schema may be restructured or migrated, name the constraint explicitly: CONSTRAINT pk_user PRIMARY KEY (email). A meaningful name makes the schema easier to read and constraint errors easier to identify. section 6.11
