SQL-Primary Key - Fullstackadda

A Primary key is a column or set of columns in a table that uniquely identifies each row in the table. It is used to enforce the integrity of the data and to ensure that each row in the table has a unique identifier. SQL-Primary Key

To use a primary key in a table, you can specify it during table creation using the PRIMARY KEY constraint. For example, if you have a table called “employees” and you want to use the “employee_id” column as the primary key, you can create the table using the following SQL statement: SQL – Primary Key

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    ...
);

You should use primary key when you want to create a unique identifier for each row in a table. Primary keys are used to enforce data integrity and to ensure that each row has a unique identifier.

You should use the primary key when you want to create a relationship between two tables. Primary keys are used as a foreign key in another table to create a relationship.

For example, consider a ‘Student’ table and a ‘Course’ table. The ‘Student’ table has a primary key ‘student_id’ and the ‘Course’ table has a primary key ‘course_id’. To create a relationship between the two tables, ‘student_id’ is used as a foreign key in the ‘Course’ table.

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(255),
    ...
);

CREATE TABLE Course (
    course_id INT PRIMARY KEY,
    name VARCHAR(255),
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES Student(student_id)
);

Here, the ‘student_id’ column in the ‘Course’ table is a foreign key that references the ‘student_id’ column of the ‘Student’ table, creating a relationship between the two tables.

When to use Primary key ? SQL-Primary Key

A primary key is used in SQL to uniquely identify each record in a table. It is a column or set of columns that enforce a unique constraint and prevent duplicate values. Primary keys are used to establish and enforce relationships between tables, and are often used as the basis for indexing and querying data. It is important to use a primary key in SQL when you want to ensure that each record in a table is unique and can be easily identified and referenced.

Primary vs Foreign

Here is a table that shows the main differences between primary keys and foreign keys in SQL:

Feature	Primary Key	Foreign Key
Definition	A column or set of columns in a table that uniquely identifies each row in the table.	A column or set of columns in a table that is used to establish a link between the data in two tables.
Constraint	Enforces the uniqueness of the values in the primary key column(s).	Enforces referential integrity by ensuring that the value of the foreign key matches a value of a primary key in another table.
Can be Null?	No (null values are not allowed in primary key columns)	Yes (null values are allowed in foreign key columns, but it’s not recommended)
Can be repeated in the same table	No (primary key values must be unique within the table)	No (foreign key values must match the primary key values of the referenced table)
Can be referenced by	None	A foreign key

SQL-Primary Key SQL-Primary Key SQL-Primary Key SQL-Primary Key