menu

SQL FOREIGN Key

SQL FOREIGN Key Constraint

A Foreign key is used to define the relation between two tables . A Foreign key points to the primary key in another table.

Suppose We Have The Following Tables :

Orders Table :

OrderTable
Note : “UserId” In “Orders” Table is the id of the users which is assigned to users in “Users” Table in “ID” column. The Values stored in “UserId” Column are referred from “Users” Table.

And

Users Table :

SQL FOREIGN Key tableFull

UserId In Orders table is the primary key in table Users ie . column ID(Primary Key).
Now to define this relation we have to make foreign key in Orders table.

There are Two Ways to Define Foreign Key :

1 . At the time of Creation of table :

Example

CREATE TABLE IF NOT EXISTS Orders (
OrderId int(11) NOT NULL,
UserId int(11) NOT NULL,
OrderItem varchar(100) NOT NULL,
Sysdate datetime NOT NULL,
CONSTRAINT usersID_FOREIGN_KEY FOREIGN KEY (UserId)
REFERENCES Users(ID);
) ;

2 . In Existing table :

Example

ALTER TABLE Orders ADD
CONSTRAINT usersID_FOREIGN_KEY FOREIGN KEY (UserId)
REFERENCES Users(ID);

Delete Foreign Key Constraint :

Example

ALTER TABLE Orders DROP
CONSTRAINT usersID_FOREIGN_KEY;

Note : In Mysql Following Query Works.

Example

ALTER TABLE Orders DROP
FOREIGN KEY usersID_FOREIGN_KEY;

Advertisements