SQL FOREIGN KEY Constraint

The PRIMARY KEY of a table exists in another table, but not as a PRIMARY KEY is called as FOREIGN KEY. Let's see the foreign key with an example. Look at the following two tables:

Department Table:

did dname

101

102

103

Development

Marketing

Testing

Employee Table:

eid ename did

E101

E102

E103

Tapaswini

Bandana

Akshya

101

102

103

Here in Department table did is the PRIMARY KEY and in Employee table eid is the PRIMARY KEY and did is the FOREIGN KEY.

SQL FOREIGN KEY Constraint on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "did" column when the
"Employee" table is created:

Oracle / SQL Server / MS Access:
CREATE TABLE dept
(
d_id int PRIMARY KEY,
d_name varchar2(100)
);
CREATE TABLE employee
(
e_id int PRIMARY KEY,
e_name varchar2(100) NOT NULL,
d_id int,
CONSTRAINT fk_deptemp
FOREIGN KEY(d_id) REFERENCES
dept(d_id)
)

MySQL:
CREATE TABLE Employee
(
eid int NOT NULL,
ename int NOT NULL,
did varchar2(100),
PRIMARY KEY (eid),
FOREIGN KEY (did) REFERENCES Department(did)
)

SQL FOREIGN KEY Constraint on ALTER TABLE

To create a FOREIGN KEY constraint on the "did" column when the "Employee"
table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Employee
ADD FOREIGN KEY (did)
REFERENCES Department(did)
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY
constraint on multiple columns, use the following SQL syntax:
Oracle / SQL Server / MySQL / MS Access:
ALTER TABLE Employee
ADD CONSTRAINT fk_DeptEmployee
FOREIGN KEY (did)
REFERENCES Persons(did)

To DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Employee
DROP FOREIGN KEY fk_DeptEmployee
Oracle/SQL Server / MS Access:
ALTER TABLE Employee
DROP CONSTRAINT fk_DeptEmployee



  Tutorials

Popular Links

Contact Us

address Plot No-741,2ND Floor
Opp. Bhagabati Temple,Jayadev Vihar
      Bhubaneswar-751013
      Email: info@silantechnology.com
      Phone: 0674-2361252
address
39877, sundale dr, apt#101, Fremont, California 94538, USA
       Phone: +1(262)388-7619

facebook twitter google linked in

© 2018 Silan Technology. All Rights Reserved