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 :
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 :
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
