• September 29, 2014
  • SQL
  • Comments Off on SQL FOREIGN KEY

SQL FOREIGN KEY

The FOREIGN KEY is used to define a relationship between two tables and a FOREIGN KEY in one table points to the PRIMARY KEY in another table.

Syntax of FOREIGN KEY Constraint on one column with CREATE TABLE statement:

MySQL:

CREATE TABLE Orders
(
Order_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (Order_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
Order_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

Syntax of FOREIGN KEY Constraint on one column with ALTER TABLE statement:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders DROP FOREIGN KEY fk_POrders

Syntax of FOREIGN KEY Constraint on one column with CREATE TABLE statement:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
Order_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (Order_Id),
CONSTRAINT fk_POrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

Syntax of FOREIGN KEY Constraint on one column with ALTER TABLE statement:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders ADD CONSTRAINT fk_POrders
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

DropFOREIGNKEY:

Use following syntax to delete the foreign key.

MySQL:

ALTER TABLE Orders DROP FOREIGN KEY fk_POrders

SQL Server / Oracle / MS Access:

ALTER TABLE Orders DROP CONSTRAINT fk_POrders

Difference between primary key and foreign key:

Primary key Foreign key
  1. 1. Primary key can’t contain null values.
  2. 2. Primary key can’t be duplicate.
  3. 3. A table can have only one primary key.
  4. 4. Primary key automatically adds a clustered index.
  1. 1. Foreign key can contain null values.
  2. 2. Foreign key can be duplicate.
  3. 3. A table can have more than one foreign key.
  4. 4. Foreign key not add any index automatically.

 
Next Topic: SQL NOT NULL Constraint with example.
Previous Topic: SQL PRIMARY KEY Constraint with example.

Related Topics:

How to build java project using ant in eclipse?
JAXB marshalling – convert java object to xml example.
How to create pdf file in java using iText jar?
Generics class example.
OGNL in struts 2.
Hibernate One-to-One Mapping using xml.
Send inline image in email using JavaMail API.
Quartz 2 JobListener example.

 


Comments are closed.