What is primary key, composite key, foreign key & Unique Constraint?

Que : What is primary key and role of primary key ?

Ans :
Primary Key : The PRIMARY KEY constraint uniquely identifies each record in a database table.
1.Primary keys must contain unique values.
2.A primary key column cannot contain NULL values.
3.Each table should have a primary key, and each table can have only ONE primary key.
4.A primary key is a field in a table which uniquely identifies the each rows/records in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.
5.A table can have only one primary key which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

PRIMARY Key Example :

In MySql :
Create Table Employee
(
Emp_id int Not Null,
Emp_name varchar(255),
Emp_designation varchar(255),
PRIMARY_KEY(Emp_id)
)
SQL Server
Create Table Employee
(
Emp_id int Not Null PRIMARY KEY,
Emp_name varchar(255),
Emp_designation varchar(255)
)

Que : What is composite key ?

Ans.: A composite key, in the context of relational databases, is a combination of two or more columns in a table that can be used to uniquely identify each row in the table.

Que : What is foreign key and role of foreign key ?

Ans :
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Let's illustrate the foreign key with an example. Look at the following two tables:
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 2
4 24562 1
Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

Foreign Key Example:

In Mysql
Create Table Employee
(
Emp_reg char(50) Not Null,
Emp_id int Not Null,
Emp_name varchar(255),
Emp_salary Decimal(10,2),
PRIMARY_KEY(Emp_reg),
FOREIGN KEY (Emp_id) REFERENCES Salary(Emp_id)
)
SQL Server
Create Table Employee
(
Emp_reg int Not Null PRIMARY KEY,
Emp_id Int FOREIGN KEY REFERENCES Salary(Emp_id)
Emp_name varchar(255),
Emp_designation varchar(255)
)

Que : What is the unique constraints?

Ans: The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

For example : CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

Please note: 1.

You can only have one primary key per table, but multiple unique constraints (249, I believe).

2.

A PK can not have a NULL value - if you try to make a NULLABlE column as your PK, SQL Server will change it to NOT NULL first, and then create the PK. A Unique constraint can have one NULL value.

Tag :Difference between Primary key and Foreign Key with example, what is unique contraint, what is composite key