Primary Key and Foreign Key in SQL

PRIMARY KEY AND FOREIGN KEY IN SQL

PRIMARY KEY (PK)

Primary Keys are the data column in a table which should be having  unique and NOT NULL data.  A table can have only one Primary key. Primary key may consist one column or more than one column. When there is more than one columns , then the uniqueness should be maintained combining all the columns involved not from each single column, ie, each row from the combined primary key columns should be unique. Index is automatically created for the primary key data. If more than one columns are involved, it is called composite primary key.  Any column which is unique and NOT Null is a candidate key, ie, candidate for Primary key.

FOREIGN KEY(FK)

Foreign Key : Foreign key is a column referencing the primary key column. The primary key could be in the same table or in different table/s.  Foreign keys can have one or more null values and duplicate values (from the primary key column).  A table can have more than one foreign keys. Same table can have primary key and foreign key.

CREATE PRIMARY KEY

Let's create a table TEST with primary key :

CREATE TABLE TEST 

(

EMP_ID   INT PRIMARY KEY  ,  --- PK means this column accepts only unique and not null values.

Location  varchar(20),

Salary  float

) ;

//OR //

You can mention primary key like below also.

(

EMP_ID   INT , 

Location  varchar(20),

Salary  float,

PRIMARY KEY (EMP_ID   )

) ;

//OR //
Another way to mention primary key , giving name to the primary key :

(

EMP_ID   INT , 

Location  varchar(20),

Salary  float,

Constraint PriKeyConsName PRIMARY KEY (EMP_ID   )

) ;
In this example, we gave a name to the primary key.

 ERROR

While adding data to this table(PK TABLE), if you try to add duplicate or NULL in the primary key column, it shows error.

The exact error message might be different depending upon the type of database but the nature of the error will be like below :

If you try to add duplicate value in PK column, it shows error like below:

Error : unique constraint  violated

If you try to add NULL in the PK column, it shows error like below :

Error : cannot insert NULL into "TEST"."EMP_ID"

Other columns can have duplicate values , as well as , NULL  like below :

EMP_ID	Location	Salary
101	MD	2000
100	Chicago	1950.65
102	NY	3000
103	NY	NULL

Foreign Key(column)  references the primary key(column).

Foreign Key maintains referential integrity between the two columns(PK and FK). 

CREATE FOREIGN KEY

CREATE TABLE INFO_EMP 

(

Batch_ID  INT REFERENCES TEST(EMP_ID) ,   --- This is foreign key

NAME varchar(20),

JOB_NUMBER INT ,

POSITION  varchar(20),

DEPARTMENT  varchar(20),

HIREDATE DATE

) ;

ERROR

In the foreign key column, you can enter  only those data which are present in the primary key column, if you try to enter different data, you get error like below :

Error : integrity constraint  violated - parent key not found --- the nature of the error will be like this but exact error message might be different depending upon the type of database.

As you can see in the below table with foreign key, the foreign key column can have data from primary key column only.

Foreign key column can ALSO  have duplicates and NULL  values.

BATCH_ID	NAME	JOB_NUMBER	POSITION	DEPARTMENT	HIREDATE
100	DAVID	143658	MANAGER	HR	10-Jan-15
103	SMITH	548900	ENGINEER	TECHNICAL	12-Dec-11
101	LINDA	784510	ASSISTANT	FINANCE	1-Jun-12
101	MIKE	543433	OPERATOR	TECHNICAL	1-Feb-12
NULL	SMITH	543433	ASSISTANT	TECHNICAL	2-Nov-13
NULL	SHARMA	543401	OFFICER	DEPLOYMENT	22-Aug-15

Same table can have primary key and foreign key :

CREATE TABLE INFO_EMP1 

(

Batch_ID  INT REFERENCES TEST(EMP_ID) , --This is foreign key

NAME varchar(20),

JOB_NUMBER INT PRIMARY KEY,  --- This is primary key

POSITION  varchar(20),

DEPARTMENT  varchar(20),

HIREDATE DATE

) ;

Let's  see  COMPOSITE  primary key :

CREATE TABLE INFO_EMP2

(

Batch_ID  INT ,

NAME varchar(20),

JOB_NUMBER INT ,

POSITION  varchar(20),

DEPARTMENT  varchar(20),

HIREDATE DATE ,

PRIMARY KEY (BATCH_ID, NAME, JOB_NUMBER)   --- Composite primary key

) ;

Combining the three columns , the data is unique in each record of the primary key, as shown below.

BATCH_ID	NAME	JOB_NUMBER	POSITION	DEPARTMENT	HIREDATE
100	DAVID	5434401	OFFICER	DEPLOYMENT	22-Aug-15
100	LINDA	5434401	MANAGER	FINANCE	20-Mar-15
100	LINDA	5433351	SPECIALIST	DESIGN	20-Mar-15
111	LINDA	5433351	ENGINEER	ROAD	10-Feb-00

FOREIGN KEY can reference PRIMARY KEY from the same table:

CREATE TABLE INFO_EMP3

(

BATCH_ID  INT PRIMARY KEY,

LOCATION  varchar(20),

NAME varchar(20),

JOB_NUMBER INT REFERENCES INFO_EMP3(BATCH_ID) ,

JOB_TYPE  varchar(20)

) ;

BATCH_ID	LOCATION	NAME	JOB_NUMBER	JOB_TYPE
111	TX	HARI	111	FULL TIME
110	VA	SMITH	111	FULL TIME
100	VA	JUDI	100	FULL TIME
112	VA	JUDI	null	FULL TIME

Adding a primary key in an existing table:
Alter Table TableName
Add constraint constraintName  Primary key (Column1, Column2,....);  You can create primary key of 1 column or more than one columns as required.


DROP Primary or Foreign Key:
To drop a primary key, you have to first drop foreign key. > This is to be confirmed.
 
To drop a primary key:
Alter Table TableName
Drop Constraint ConstraintName ; > this constraint name is name of primary key constraint. This method can be used to drop any constraints from the table.

//OR//
Alter Table TableName
Drop Primary Key;

You can Enable/Disable a primary key as well using the Alter command with Enable or Disable commands.