SQL Primary Keys

Definition

In SQL, a primary key is a column or set of columns that uniquely identifies each row in a table. The primary key is used to establish and enforce relationships between tables in a relational database. It must contain unique values and cannot contain null values. A table can have only one primary key, which may consist of single or multiple fields. In SQL a primary key is defined on one or more columns of a table, and it enforces the integrity of the data by preventing duplicate values and ensuring that each row can be uniquely identified.

Example

Here's an example of how to create a table with a primary key using the SQL CREATE TABLE statement:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

In this example, the id column is the primary key of the table. The PRIMARY KEY constraint ensures that the values in the id column are unique and cannot be null.

you can also add the primary key constraint after table is created by using the following SQL statement:

ALTER TABLE customers 
ADD PRIMARY KEY (id);

You can also create a primary key on multiple columns, known as composite primary key by using the following query:

CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    role_grant_date DATE NOT NULL,
    PRIMARY KEY (id, customer_id)
);

In this example, the primary key is a composite key made up of the user_id and role_id columns. Together, these columns uniquely identify each row in the table.

Best Practices

There are a few best practices to keep in mind when creating a primary key in SQL:

• Use a single column as the primary key, if possible. Composite keys (keys made up of multiple columns) can be more complex to work with and may not perform as well as a single-column key.

• Choose a column with unique values as the primary key. This ensures that each row in the table can be identified by a distinct value.

• Use an integer data type for the primary key, if possible. Integer keys are more efficient and take up less storage space than other data types.

• Use an auto-incrementing value for the primary key, if possible. This eliminates the need for manually assigning unique values to the key column and ensures that new rows are given unique key values automatically on creation.

• Avoid using sensitive data as primary key, such as Social Security Numbers, ID Numbers etc.

• Be careful when repopulating the table or changing the primary key, because it can cause data integrity issues and cascade issues with foreign keys in other tables.