SQL PRIMARY KEY，SQL FOREIGN KEY

A primary key is defined as a column or a group of column that their value are always be unique. Normally, NULL value will never be allowed in this column to be part of this column’s records.

EXAMPLE :

Let’s say we want to create a table name Users. The PRIMARY KEY will be user_id for that table.

SQL statement is:

CREATE TABLE USERS (
user_id SMALLINT NOT NULL PRIMARY KEY,
user_name VARCHAR(50),
)

OR

CREATE TABLE USERS (
user_id SMALLINT NOT NULL ,
user_name VARCHAR(50),
PRIMARY KEY(user_id)
)

 

 

Foreign key is use to referential to the unique parent table.
Foreign Key will point to the Primary key of the Parent table.
This will not allow primary from parent table to be deleted without clearing the record of the child table.

 

EXAMPLE :

 

Let’s say we want to create 2 tables name users and departments.
and have to create FOREIGN KEY in users table to link to the department table

 

SQL statement :

 

CREATE TABLE DEPARTMENTS (
department_id SMALLINT NOT NULL PRIMARY KEY,
department_name VARCHAR(50),
)

 

And the users table will be like this:

 

SQL statement is:

 

CREATE TABLE USERS (
user_id SMALLINT NOT NULL PRIMARY KEY,
user_name VARCHAR(50),
department_id SMALLINT NOT NULL ,
FOREIGN KEY (department_ID) references DEPARTMENTS
)