CodesJava

Easy learning with example program codes

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. Primary key can’t contain null values.
  2. Primary key can’t be duplicate.
  3. A table can have only one primary key.
  4. Primary key automatically adds a clustered index.
  1. Foreign key can contain null values.
  2. Foreign key can be duplicate.
  3. A table can have more than one foreign key.
  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.

 

Sign Up/ Sign In
Ask a Question


Industrial Training

We offers Placement Oriented Training on Java, Spring, JSF, Hibernate, PHP, AngularJS, Angular 4, PLSQL, Oracle BI Publisher etc. We also provides Online training, please mail us at hr@codesjava.com.

Development

We also provides the Development services for Website Development , Java Development, PHP Development, Android App Development etc. You can contact us on hr@codesjava.com.

Copyright © 2018 CodesJava DMCA.com Protection Status SiteMap Reference: Java Wiki