blog09

In SQL, keys are used to identify specific rows in a table and to find, or create, a relationship between tables. A key can be a single or combination of multiple fields in a table. Keys are also used to retrieve records (data rows) from the data table according to the condition or requirement.

Primary Key

The primary key in SQL is a single, or a group of fields (columns) that can uniquely identify a row in a table. The properties of each primary key column or columns are:

It enforces uniqueness by not accepting any duplicate values
A primary key uniquely identifies each field
A table can only take one primary key
A primary key column cannot accept null values
A single-column primary key is a simple one. The one consisting of multiple columns is called a composite primary key
A primary key can be created at a column or table level, using CREATE TABLE or ALTER TABLE statements

Adding Primary Key to Table

CREATE TABLE fabric(
fabric_id int not null PRIMARY KEY,
fabric_type varchar(50) not null,
fabric_name varchar(50)
);

CREATE TABLE fabric(
fabric_id int not null,
fabric_type varchar(50) not null,
fabric_name varchar(50)
PRIMARY KEY (fabric_id)
);

A primary key can also be defined after the table is created by:

ALTER TABLE fabric
ADD CONSTRAINT pk_fabric PRIMARY KEY (fabric_id);

Back to index of Keys in SQL

Super Key

A super key is a set of columns that uniquely identifies each row in a table. It is a combination of two or more columns that can be used to identify a record uniquely in a table. Various super keys together makes the criteria to select the candidate keys. A super key is a superset of the candidate key. The role of the super key is simply to identify the tuples of the specified table in the database.

Back to index of Keys in SQL

Candidate Key

A candidate key is an attribute or set of attributes that can uniquely identify a tuple. A candidate key can be used as a primary key. A table can have multiple candidate keys and one can be selected as a primary key.

CREATE TABLE employees(
id PRIMARY KEY not null
first_name varchar(100),
last_name varchar(100),
email varchar(260) not null,
ssn integer not null,
address varchar(200)
)

Back to index of Keys in SQL

Unique Key

A unique key is a set of one or more fields (columns) of a table that uniquely identify a record (row). It can accept one null value but it cannot have duplicate values. There can also be many unique key constraints for a table whereas a PRIMARY KEY has one constraint.

CREATE TABLE employees(
id int not null UNIQUE,
first_name varchar(100),
last_name varchar(100) NOT NULL
);

Back to index of Keys in SQL

Composite Key

A composite key, which is also called a compound or concatenated key, is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. A composite key cannot be null.

CREATE TABLE sales_order(
customer_id varchar(6) not null,
order_id int not null,
fabric_name varchar(150) not null,
primary key (customer_id, fabric_id)
);

Back to index of Keys in SQL

Alternate Key

An alternate key is part of a candidate key but not part of the primary key. Alternate keys do not allow duplicate values, can contain null values unless the NOT NULL constrain is set, and a tale can have more than one alternate key.

CREATE TABLE CUSTOMERS(
ID INT,
NAME VARCHAR (20),
AGE INT,
MOBILE_NO BIGINT,
PRIMARY KEY(ID)
);

Back to index of Keys in SQL

Foreign Key

A Foreign Key is a column in a table that is used as the Primary Key in another table. It is able to accept multiple nulls and duplicate values.

CREATE TABLE Department(
dept_id int PRIMARY KEY,
name varchar(50) NOT NULL,
location varchar(200) NOT NULL
)
					  
CREATE TABLE Students (
id primary key not null,
first_name varchar(100),
last_name varchar(100),
email varchar(255) not null,
registration_no not null Unique,
ssn integer not null,
residence varchar(200),
dept_id int FOREIGN KEY REFERENCES Department(dept_id)
)

Back to index of Keys in SQL

Carissa O'Connell