SQL Keys

SQL Keys are used to uniquely identify records in database tables. SQL Keys in DBMS are created using single or multiple columns to uniquely identify the records in the table. SQL Keys are used to quickly retrieve data and establish relationships between different tables.

 

SQL Keys

Types of SQL Keys

  • Primary key
  • Foreign Key
  • Unique key

Primary key

The primary key uniquely identifies a table’s record or row. We can define only one primary on a table. We define the primary key at the time of table creation. We can define the index on the primary key or it will consider the default index which is clustered index.

Properties of Primary key

  • It uniquely identifies record or row
  • It contains no null value
  • It contains a default clustered index

Syntax and example

In the below example, we have created a new productinfo table where it contains columns prodid,prodname,storename sequentially representing productid, product name, and their respective store name. the primary key is defined on the prodid column which means prodid column will uniquely identify data in the table and it will not allow duplicate values.

Syntax:-
create table TABLENAME (column1 datatype,column2 datatype,columnn datatype,primary key(column1));

Example:-
create table productinfo(
prodid bigint,
Prodname varchar(500),
storename varchar(250),
primary key(prodid)
);

INSERT INTO productinfo (prodid,Prodname,storename) VALUES (1, 'na07sha', 'pupk');


Dropping Primary key

We can drop the primary key as below

Syntax and example

syntax
ALTER TABLE TABLENAME DROP PRIMARY KEY;
example 
ALTER TABLE productinfo DROP PRIMARY KEY;

Foreign Key

Foreign Key is a key that is the primary key of one table referred to as a Foreign key in another table. Or we can also say that the Primary key of one table act as a Foreign key in another table. It establishes a relationship between two tables. In SQL keys, Foreign Key prevents dropping a table or deleting data from a table. We can have around 253 Foreign Keys in a single table.

Properties of Foreign key

  • It is useful in data normalization.
  • It reduces duplicate data.
  • The primary key table is known as the parent table while the Foreign Key table is known as the child table.
  • Establishes the relationship between the two tables.

Syntax and example

In the above example, we have referred productinfo table for the same table we have created its associated table that is storeinfo. Basically,storeinfo table contains data about the store. In which it has productid,storeid,storetype and address columns.

The prodid used in storeinfo is a FOREIGN KEY that provides store details to the end user after establishing the relationship with productinfo based on the key column prodid.

syntax

CREATE TABLENAME (COLUMNNAME FOREIGN KEY REFERENCES TABLENAME(COLUMNNAME));

example
  

create table productinfo(
prodid bigint,
Prodname varchar(500),
storename varchar(250),
primary key(prodid)
);

create table storeinfo(
prodid bigint,
storeid bigint FOREIGN KEY REFERENCES productinfo(storeid),
storetype varchar(500),
address varchar(250),
primary key(storeid)
);

 

We can also add a foreign key after table creation.

Syntax and example

syntax
ALTER TABLE TABLENAME ADD CONSTRAINT FK_prodinfo FOREIGN KEY(COLUMNNAME) REFERENCES TABLENAME(COLUMNNAME);
example
ALTER TABLE storeinfo ADD CONSTRAINT FK_prodinfo FOREIGN KEY(prodid) REFERENCES productinfo(prodid);

Dropping foreign key

We can drop foreign key as below.

Syntax and example

syntax
ALTER TABLE TABLENAME DROP foreginkeyname;

example
ALTER TABLE storeinfo DROP fk_prodid;

Unique key
A unique key defines uniqueness on a table column or it doesn’t allow duplicate values in a column. In SQL keys we can have only one null value in the unique key column. In SQL keys We can have more than one Unique key column in a single table. mobile number, email id can have a unique key.

Properties of Unique key

  • The unique key contains only one null value.
  • It doesn’t store duplicate data.
  • It can be referred to as the foreign key in another table.
  • We can have multiple unique keys on a single table.

Syntax and example

The below table contains employee information in which mob no is the mobile number that will be unique.

syntax

CREATE TABLE TABLENAME(COLUMNNAME UNIQUE,COLUMN..N);

example

create table empinfo(
empid bigint,
empname varchar(500),
mobno int unique,
primary key(prodid)
);

Dropping unique key

syntax
ALTER TABLE TABLENAME DROP CONSTRAINT UNIQUEKEYNAME;


example

ALTER TABLE empinfo DROP CONSTRAINT uq_mobno;