Keys in SQL

In SQL, a key is a field or set of fields that uniquely identifies a row in a table. There are several types of keys that can be used in a database, including primary keys, foreign keys, and unique keys.

Primary Key

A primary key is a field or set of fields that uniquely identifies a row in a table. It cannot contain null values and must be unique across all rows in the table. A table can have only one primary key, and it is typically used to ensure data integrity and to establish relationships with other tables through foreign keys.

Here is an example of how to define a primary key in SQL:

CopyCREATE TABLE customers ( customer_id INT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255) ); 

In this example, the customer_id field is defined as the primary key for the customers table.

Foreign Key

A foreign key is a field or set of fields in a table that refers to the primary key of another table. It is used to establish a relationship between the two tables and to enforce data integrity by ensuring that the values in the foreign key field or fields must match the values in the primary key of the related table.

Here is an example of how to define a foreign key in SQL:

CopyCREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, product_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id), FOREIGN KEY (product_id) REFERENCES products(product_id) ); 

In this example, the orders table has two foreign keys: customer_id and product_id. These fields refer to the primary keys of the customers and products tables, respectively.

Unique Key

A unique key is a field or set of fields that must be unique across all rows in a table, but it can contain null values. It is used to enforce data integrity by ensuring that no two rows in the table have the same values in the unique key field or fields.

Here is an example of how to define a unique key in SQL:

CopyCREATE TABLE products ( product_id INT PRIMARY KEY, name VARCHAR(255) UNIQUE, price DECIMAL ); 

In this example, the name field is defined as a unique key for the products table. This means that the name field must be unique across all rows in the table, but it is allowed to contain null values.

Composite Key

A composite key is a primary key or unique key that is made up of two or more fields. It is used to uniquely identify a row in a table when a single field is not sufficient.

Here is an example of how to define a composite primary key in SQL:

CopyCREATE TABLE sales ( product_id INT, store_id INT, sale_date DATE, PRIMARY KEY (product_id, store_id, sale_date) ); 

In this example, the sales table has a composite primary key that is made up of the product_id, store_id, and sale_date fields. This means that the combination of these three fields must be unique across all rows in the table.

Natural Key

A natural key is a field or set of fields that already exists in a table and can be used to uniquely identify a row. It is often used as the primary key for a table because it is already present in the data and does not need to be created specifically for the purpose of identifying rows.

Here is an example of how to define a natural key as the primary key for a table:

CopyCREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255) UNIQUE ); 

In this example, the employee_id field is a natural key that is used as the primary key for the employees table. The email field is also a natural key, but it is defined as a unique key rather than a primary key.

Surrogate Key

A surrogate key is a synthetic key that is created specifically for the purpose of identifying rows in a table. It is often used as the primary key for a table when there is no natural key that can be used.

Here is an example of how to define a surrogate key as the primary key for a table:

CopyCREATE TABLE products ( product_id INT PRIMARY KEY, name VARCHAR(255), price DECIMAL ); 

In this example, the product_id field is a surrogate key that is used as the primary key for the products table. This field does not have any meaning in the real world, but it is used to uniquely identify rows in the table.

Referential Integrity

Referential integrity is the concept of ensuring that relationships between tables are maintained. This is achieved through the use of foreign keys and constraints. When referential integrity is enforced, you cannot delete a row from a table if it is referenced by a foreign key in another table. You can also specify cascading actions, such as deleting or updating related rows when a row is deleted or updated in the parent table.

Here is an example of how to enforce referential integrity in SQL:

CopyALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES parent_table (parent_column) ON DELETE CASCADE; 

In this example, a foreign key is added to the table_name table, and referential integrity is enforced by specifying that related rows in the parent_table should be deleted when a row is deleted from the table_name table.