Tools Tags

SQL Tutorial Introduction to SQL SQL syntax SQL SELECT SQL SELECT DISTINCT SQL WHERE SQL AND & OR SQL ORDER BY SQL INSERT INTO SQL UPDATE SQL DELETE

SQL Advanced Tutorial

SQL SELECT TOP SQL LIKE SQL wildcard SQL IN SQL BETWEEN SQL alias SQL connection (JOIN) SQL INNER JOIN SQL LEFT JOIN SQL RIGHT JOIN SQL FULL OUTER JOIN SQL UNION SQL SELECT INTO SQL INSERT INTO SELECT SQL CREATE DB SQL CREATE TABLE SQL constraints SQL NOT NULL SQL UNIQUE SQL PRIMARY KEY SQL FOREIGN KEY SQL CHECK SQL DEFAULT SQL CREATE INDEX SQL DROP SQL ALTER SQL AUTO INCREMENT SQL view SQL date SQL NULL value SQL NULL function SQL common data types SQLDB data type

Home

database

SQL Server

SQL Getting Started Tutorial Manual

SQL FOREIGN KEY

Collection 127

update time 2016-10-15

SQL PRIMARY KEY

SQL CHECK

SQL FOREIGN KEY constraint

The FOREIGN KEY in one table points to the PRIMARY KEY in another table.

Let us explain foreign keys through an example. Please look at the following two tables:

"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

"Orders" table:

##O_IdOrderNoP_Id 177895324467833224562##4

Please 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 the FOREIGN KEY in the "Orders" table.

FOREIGN KEY constraints are used to prevent behavior that destroys connections between tables.

FOREIGN KEY constraint also prevents illegal data from being inserted into a foreign key column because it must be one of the values in the table it points to.

SQL FOREIGN KEY constraint when CREATE TABLE

The following SQL creates a FOREIGN KEY constraint 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 name the FOREIGN KEY constraint and define FOREIGN KEY constraints for multiple columns, please 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 constraints when ALTER TABLE

when" Orders" table has been created, if you need to create a FOREIGN KEY constraint on the "P_Id" column, please use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders

ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

If you need to name the FOREIGN KEY constraint and define FOREIGN for multiple columns KEY constraints, please 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)

Revoke FOREIGN KEY constraint

If you need to revoke FOREIGN KEY constraint, please use SQL below:

MySQL:

ALTER TABLE Orders

DROP FOREIGN KEY fk_PerOrders

SQL Server/Oracle/MS Access:

ALTER TABLE Orders

DROP CONSTRAINT fk_PerOrders

submit

SQL PRIMARY KEY

SQL CHECK

Related videos

Free

IntermediateThe latest ThinkPHP 5.1 world premiere video tutorial (60 days to become a PHP expert online training course)

496486

Free

ElementaryLet's briefly talk about starting a business in PHP

18870

Free

Large-scale practical Tianlongbabu development of Mini version MVC framework imitating the encyclopedia website of embarrassing things

IntermediateLarge-scale practical Tianlongbabu development of Mini version MVC framework imitating the encyclopedia website of embarrassing things

45369

Free

ElementaryQuick introduction to web front-end development

157089

Free

ElementaryLogin verification and classic message board

31058

Free

IntermediateGetting Started with PHP Practical Development: PHP Quick Creation [Small Business Forum]

81486

Hot AI Tools

Undress AI Tool

Undress images for free

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undresser.AI Undress

AI-powered app for creating realistic nude photos

ArtGPT

AI image generator for creative art from text prompts.

Stock Market GPT

AI powered investment research for smarter decisions

Hot Article

Windows10 cannot connect to the Internet_Win10 network reset command detailed explanation 3 weeks ago By DDD

How to resolve 'Windows cannot find a driver for your network adapter' (Ethernet)? 1 months ago By 下次还敢

How to add subtitles in premiere 1 months ago By 下次还敢

How to add hot tags to video accounts? What is the use of hotspot tags? 1 months ago By DDD

How to set Win11 not to display frequently used folders_Windows11 Quick Access Settings 2 weeks ago By DDD

Hot Topics

# Douyin level price list 1-75

20464

# wifi shows no ip assigned

13602

# Virtual mobile phone number to receive verification code

11942

# Where is the login entrance for gmail email?

8947

# How to turn off windows security center

8487

Popular tool

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)





24562	1