SQL Keys - Primary Key

Syntax

The syntax for creating a primary key in SQL is:

CREATE TABLE table_name
(
    column1 data_type PRIMARY KEY,
    column2 data_type,
    column3 data_type,
    ...
);

Example

Let's say we have a table called employees and we want to make the employee_id column the primary key. We would use the following code:

CREATE TABLE employees
(
    employee_id int PRIMARY KEY,
    first_name varchar(255),
    last_name varchar(255),
    email varchar(255),
    hire_date date
);

Output

The output of the above code would be a table called employees with a primary key on the employee_id column.

Explanation

A primary key is a column or combination of columns that uniquely identifies each row in a table. The primary key constraint ensures that the values in the primary key column(s) are unique, meaning that no two rows can have the same primary key value. Additionally, a primary key cannot contain null values.

Using a primary key is important because it allows for efficient searching and sorting of data, and helps to prevent duplicate data from being entered into the table.

Use

Primary keys are typically used in relational databases to establish relationships between tables. For example, if we have a table of orders and a table of customers, we might use the customer_id column as the primary key in the customers table and as a foreign key in the orders table to establish a relationship between the two tables.

Important Points

• A primary key is a unique identifier for each row in a table.
• Primary keys must be unique, non-null, and cannot be changed once they are set.
• A table can only have one primary key.
• Foreign keys can reference the primary key of another table.

Summary

A primary key is an important concept in SQL and relational databases. By uniquely identifying each row in a table, primary keys help to ensure data integrity and make it easier to establish relationships between tables. When creating tables in SQL, it's important to carefully consider which columns should be used as primary keys and to set these constraints correctly to ensure the integrity of the data.