Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle FOREIGN KEY Constraint

In Oracle, FOREIGN KEY constraint is used to link-up two or more tables. The table in which the foreign key is defined is called the "child table" and it (often) refers to the primary key in the parent table.

Oracle FOREIGN KEY Constraint example

The following Oracle, creates a FOREIGN KEY constraint on the "bankId" column when the "tblbankdemataccountcharges" table is created:

Example

//Primary key table
CREATE TABLE tblbankheadquartersandaddress (
    pk int NOT NULL PRIMARY KEY,
    firstName varchar(255) NOT NULL,
    lastName varchar(255) NOT NULL,
    address varchar(255) NOT NULL,
);

//Foreign key table
CREATE TABLE tblbankdemataccountcharges (
    bankId int NOT NULL,
    firstName varchar(255) NOT NULL,
    lastName varchar(255) NOT NULL,
    address varchar(255) NOT NULL,
	FOREIGN KEY (bankId) REFERENCES tblbankheadquartersandaddress(pk)
);

The "pk" column in the "tblbankheadquartersandaddress" table is the PRIMARY KEY in the "tblbankheadquartersandaddress" table.

The "bankId" column in the "tblbankdemataccountcharges" table is a FOREIGN KEY in the "tblbankdemataccountcharges" table.

Oracle FOREIGN KEY Constraint example on ALTER table

The following Oracle, creates a FOREIGN KEY constraint on the "custId" columns when the "tblbankdemataccountcharges" table already exists:

Example

ALTER TABLE tblbankdemataccountcharges
ADD FOREIGN KEY (custId) REFERENCES tblbankheadquartersandaddress(pk);

Oracle DROP a FOREIGN KEY Constraint example

The following Oracle, dropping FOREIGN KEY constraint "FK_BankID" from "tblbankdemataccountcharges" table:

Example

ALTER TABLE tblbankdemataccountcharges
DROP FOREIGN KEY FK_BankID;