Explain the different types of key constraints used in the SQL table with their examples.
Explain the different types of keys used in the SQL table with examples.
18224-Mar-2023
Updated on 28-Apr-2023
Aryan Kumar
28-Apr-2023In SQL, keys are used to identify unique records in a table and establish relationships between tables. There are different types of keys used in SQL tables, including:
In this example, the id column is the primary key for the customers table.
In this example, the customer_id column in the orders table is a foreign key that refers to the id column in the customers table.
In this example, the sku column is a unique key for the products table, which means that it can contain null values but must be unique for each record.
In this example, the combination of the order_id and product_id columns is used as a composite primary key for the order_items table.
In summary, SQL tables use different types of keys to identify unique records, establish relationships between tables, and maintain data integrity. These keys include primary keys, foreign keys, unique keys, and composite keys.
Krishnapriya Rajeev
24-Mar-2023In SQL, different types of keys are used to establish relationships between the tables and ensure integrity. Some of the commonly used keys include primary keys, foreign keys, unique keys, composite keys, and candidate keys.
In this example, id column is the PRIMARY KEY.
2. FOREIGN KEYS: A foreign key is a type of column found in a table that utilizes the values present in the primary key of a separate table. The values in this column can either be an already existing primary key from the referenced table or null and cannot be anything else.
In this example, emp_id acts as a foreign key in the table ‘DEPENDANTS’ that refers to the primary key of the table ‘EMPLOYEES’.
3. UNIQUE KEYS: A unique key is a column in a table that has a unique constraint applied to it. It can have null values but no two values can be the same.
In this example, ‘position’ column is a unique key.
4. COMPOSITE KEYS: A composite key consists of two or more columns that together uniquely identify each row in a table.
In this example, the composite key is made up of the ‘emp_id’ and ‘mobile’ columns in the ‘EMPLOYEES’ table.
5. CANDIDATE KEY: A candidate key is a column or a group of columns that could potentially be used as a primary key and must be unique and not contain null values.
In this example, the ‘mobile’ column is a candidate key that could potentially be used as the primary key.