A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Let’s illustrate the foreign key with an example. Look at the following two tables:
The “Persons” table:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
The “Orders” table:
| O_Id | OrderNo | P_Id |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 2 |
| 4 | 24562 | 1 |
Note that the “P_Id” column in the “Orders” table points to the “P_Id” column in the “Persons” table. The “P_Id” column in the “Persons” table is the PRIMARY KEY in the “Persons” table. The “P_Id” column in the “Orders” table is a FOREIGN KEY in the “Orders” table. The FOREIGN KEY constraint is used to prevent actions that would destroy link between tables. The FOREIGN KEY constraint also prevents that invalid data is inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
SQL FOREIGN KEY Constraint on CREATE TABLE
The following SQL creates a FOREIGN KEY on the “P_Id” column when the “Orders” table is created:
MySQL:
CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) )
SQL Server / Oracle / MS Access:
CREATE TABLE Orders ( O_Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, P_Id int FOREIGN KEY REFERENCES Persons(P_Id) )
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) )
SQL FOREIGN KEY Constraint on ALTER TABLE
To create a FOREIGN KEY constraint on the “P_Id” column when the “Orders” table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
To DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access:
ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders
Incoming search terms for the article:
sql foreign key constraint, access 2010 sql foreign key, what is this CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES, foreign key sql access 2010, CONSTRAINT fk_PerOrders, ms sql add foreign key constraint, ms access foreign key references, ms access foreign key constraint, ms access alter table add fk not null, ms access 2010 - foreign key reference, microsoft access alter table foreign key multiple, how use fk in sql 2010, how to create table foreign key sql server, how to create multiple foreign keys in sql table, how to create a table in access 2010 sql with primary key and foreign key together, how to add foreign key in sql server 2010, how to access foreign key sql with constraint, how add constraint in Access 2010, MS SQL constraint, ms sql foreign key on multiple columns, multiple foreign key references in ms access 2010, what is an invalid foreign key constraint in MSS, sql why use foreign key constraint, sql server foreign key constraint on multi column primary key, sql server 2010 create table foreign key constraint, sql server 2010 create table foreign key, sql foreign key constraint with multipe columns, sql for access 2010, sql add foreign key constraint, sql 2010 create foreign key, p_id int foreign key references persons(p_id), orders and persons tables in which sql?, oracle create table with foreign key constraint on multiple columns, mysql one to one sql foreign key create table, multiple foreign keys sql server, hora en sql, foreign key sql server, alter table add multiple foreign key in oracle sql, add foreign keys sql oracle not mandatory, add foreign key sql, add constraint Ms sql, access sql foreign key reference multiple, access sql foreign key constraint, Access SQL constraint foreign key multiple, access FOREIGN KEY, access alter table FOREIGN KEY sql, access 2010 sql type constraint foreign key, access 2010 sql foreign and primary keys, access 2010 sql create table fields references, access 2010 primary key sql alter, access 2010 foreign key constraint, alter table delete foreing key sql2010, CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id), CONSTRAINT fk_PerOrders sql, foreign key sql in access 2010, foreign key one to one ms access sql, foreign key multiple columns mssql, foreign key ms sql, foreign key constraint on multiple columns in sql server, FOREIGN KEY constraint ms sql server multiple column, foreign key constraint, fk_PerOrders, defining a FOREIGN KEY constraint on multiple columns in Sql, CREATE TABLE Orders ( Of Id int NOT NULL Order No int NOT NULL P_Id int PRIMARY KEY (O_Id) CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ), create foreign key field referenced to existing column access 2010, constraint sqlserver2010, constraint foreign key microsoft access, constraint foreign key access, access 2010 ALTER TABLE INTEGER PRIMARY KEY
