In this blog, I’m explaining the keys in sql and how to create it.
Primary Key
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.
Leave Comment