Explain the different types of keys used in the SQL table with examples.

Question

Aryan Kumar · Answer

In 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:

Primary Key: A primary key is a unique identifier for each record in a table. It cannot contain null values and must be unique for each record. The primary key is used to create relationships between tables. Example:

CREATE TABLE customers (
 id INT PRIMARY KEY,
 name VARCHAR(50),
 email VARCHAR(50)
);

In this example, the id column is the primary key for the customers table.

Foreign Key: A foreign key is a field in one table that refers to the primary key in another table. It is used to establish relationships between tables. Example:

CREATE TABLE orders (
 id INT PRIMARY KEY,
 customer_id INT,
 total_price DECIMAL(10,2),
 FOREIGN KEY (customer_id) REFERENCES customers(id)
);

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.

Unique Key: A unique key is similar to a primary key in that it identifies unique records in a table, but it can contain null values. Example:

CREATE TABLE products (
 id INT PRIMARY KEY,
 name VARCHAR(50),
 sku VARCHAR(20) UNIQUE,
 price DECIMAL(10,2)
);

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.

Composite Key: A composite key is a combination of two or more columns that are used as a unique identifier for each record in a table. Example:

CREATE TABLE order_items (
 order_id INT,
 product_id INT,
 quantity INT,
 PRIMARY KEY (order_id, product_id),
 FOREIGN KEY (order_id) REFERENCES orders(id),
 FOREIGN KEY (product_id) REFERENCES products(id)
);

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 · Answer

In 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.

PRIMARY KEYS: A primary key uniquely identifies each row in a table and cannot contain null values. Each table can have only one primary key.

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.

forum

Explain the different types of keys used in the SQL table with examples.

Can you answer this question?

2 Answers