Primary Key
A primary key in SQL (Structured Query Language) is a special field or column or combination of columns within a table that uniquely identifies each record (row) in that table. It is used to uniquely identify all the rows of the table. In a table with a primary key, no two rows have the same primary key value.
Syntax:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100) UNIQUE
);
Things primary key signifies in a table –
Uniqueness: This ensures that no two rows in the table can have the same primary key value.
Non-null: A primary key field cannot contain NULL values.
Indexed: Typically, a primary key is automatically indexed by the database management system (DBMS). This indexing helps improve the speed of data retrieval operations. Indexing may not be very beneficial for speed when it comes to small database but they make a huge difference when it comes to big database.
Promotes normalization: Primary keys are used to establish relationships between tables in a relational database. Primary keys are connected with foreign keys to form relationships with other tables of the database.
Single or Composite Key: A primary key can created using one column or a combination of columns.
Example of primary Keys – Roll number, Employee number, phone number, E-mail, etc.
Foreign Key
In SQL, a foreign key is an attribute or a set of attributes in a database table that is used to establish a link between that table and another table. A Foreign key is a primary key of some other table. It is used to create a relationship between the two tables.
The table containing the foreign key is referred to as the referencing table, while the table it references is called the referenced table.
Syntax:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
-- Define the foreign key constraint
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
Foreign keys signifies:
Promotes Normalization: Primary keys are used to establish relationships between tables in a relational database. Primary keys are connected with foreign keys to form relationships with other tables of the database.
Relationships: Foreign keys are used to define relationships between tables in a relational database.
Child-Parent Relationship: The table containing the foreign key is often referred to as the child table, and the table it references is referred to as the parent table
Example for Foreign Keys – Similar to primary key like phone numbers, E-mails, ID, employee number, etc.
Read More: Not-NULL Constraints
