SQL FOREIGN KEY Constraint :

In my previous article i have given the idea about the Primary key constraint with different real life examples.SQL Foreign key constraint is used to define relationship between multiple tables. This is also mostly used constraint in case of data modeling and reporting in data warehouse.SQL Foreign key constraint is important constraint which will define the relationship between the tables.This article gives you idea about SQL Foreign key constraint with multiple examples which are used in real life.

SQL FOREIGN KEY Constraint in one table points to a PRIMARY KEY in another table.

How to add SQL Foreign key Constraint?

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

SQL FOREIGN KEY Constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

TO find Columns on which Constraints are declared:

SELECT Column_Name, Constraint_Name FROM User_Cons_Columns;

SELECT Column_Name, Constraint_Name, Table_Name

FROM User_Cons_Columns WHERE Table_Name=’STUDENT;

Syntax:

 CREATE TABLE <Table_Name>

(

Column1 Data_Type (size) REFERENCES <Parent_Table_Name> (Column_Name), [Column_Level]

Column2 Data_Type (size),

Column3 Data_Type (size),

CONSTRAINT <Constraint_Name> FOREIGN KEY (Column_Name)

REFERENCES <Parent_Table_Name> <Column_Name> [Table Level]

);

Example:

CREATE TABLE Student3FK

(

Class Number (5),

RollNo Number (10) REFERENCES Student (RollNo),

Subject Varchar2 (15),

Fees Number (10, 2)

);

CREATE TABLE Student3FK

(

Class Number (5),

RollNo Number (10),

Subject Varchar2 (15),

Fees Number (10, 2),

FOREIGN KEY (RollNo) REFERENCES Student3 (RollNo)

);

CREATE TABLE Student3AFK

(

Class Number (5),

FName Varchar2 (15),

LName Varchar2 (15),

Subject Varchar2 (15),

Fees Number (10, 2),

CONSTRAINT FK_Student3AFK_FName_LName FOREIGN KEY (FName, LName)

REFERENCES Student3A (FName, LName)

);

To DROP a FOREIGN KEY Constraint:

Syntax:

 ALTER TABLE <Table_Name>

DROP CONSTRAINT <Constraint_Name>;

 

Example:

ALTER TABLE Student3FK

DROP Constraint SYS_C005028;

ALTER TABLE Student3AFK

DROP CONSTRAINT FK_Student3AFK_FName_LName;

SQL FOREIGN KEY Constraint on ALTER TABLE:

To create a FOREIGN KEY Constraint on the “RollNo” Column when the “Student3” table is already created, use the following SQL:

Syntax:

 ALTER TABLE Student3FK

ADD FOREIGN KEY (RollNo) REFERENCES Student3 (RollNo);

To allow naming of a FOREIGN KEY Constraint, and for defining a FOREIGN KEY Constraint on multiple Columns, use the following SQL Syntax:

 Syntax:

 ALTER TABLE Student3AFK

ADD CONSTRAINT FK_Student3AFK_FName_LName

FOREIGN KEY (FName, LName) REFERENCES Student3A (FName, LName);

These are some important examples of adding removing foreign key in SQL.The SQL Foreign key Constraint is integral part of design the database.Hope you like this article on SQL Foreign key with examples.