A Primary Key is a column which has unique values in the column. A primary key uniquely identifies the each record in the table. A Primary key column cannot have null values. A primary key can be made up of single column or multiple columns.

Syntax:

CREATE TABLE tablename(column name datatype(size) primary key,column name datatype(size))

CREATE TABLE tablename(column name datatype(size) ,column name datatype(size),primary key(column name))

Example

CREATE TABLE student(student_id int primary key,student_name varchar(30),student_address varchar(30),student_mobile int,
student_branch varchar(10),student_date_of_birth date,);
CREATE TABLE student(student_id int,student_name varchar(30),student_address varchar(30),student_mobile int,
student_branch varchar(10),student_date_of_birth date,primary key(student_id));

Composite Key

When we have a Primary Key of a table defined using more than one columns then it is known as a Composite Key, each columns data can be duplicated, but combined values cannot be. The columns which are participating in a composite primary key are not simple keys.

Syntax:

CREATE TABLE tablename(column name datatype(size) ,column name datatype(size),column name datatype(size),primary key(column name, column name))

Example

CREATE TABLE book(name varchar(30),author varchar(30),dept varchar(30),primary key(name,author));

Foreign Key

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. A column of one table points to the Primary Key column of another table to implement referential data integrity. Foreign keys define a relationship between two tables. A foreign key identifies a column or group of columns in one (referencing) table that refers to a column or group of columns in another (referenced) table.

Syntax:

CREATE TABLE tablename(column name datatype(size), column name datatype(size),column name datatype(size), column name datatype references tablename);

Example

CREATE TABLE book(name varchar(30),author varchar(30),dept varchar(30),student_id int references student);

Candidate Key

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.  A Column (or) Combination of columns which can help uniquely identify a record in a table without the need of any external data is called a Candidate Key. Depending on the need and situation a Table may have one or more candidate keys and one of them can be used as a Primary Key of the table.

Example

CREATE TABLE student(student_id int primary key,student_roll_number int,student_enrollment_number int,student_name varchar(30),student_address varchar(30),student_mobile int,
student_branch varchar(10),student_date_of_birth date,);

In this query, student_roll_number and student_enrollment_number can be used as candidate key.