SQL FOREIGN KEY

Last update on March 28 2024 12:50:58 (UTC/GMT +8 hours)

FOREIGN KEY

The SQL FOREIGN KEY CONSTRAINT is used to ensure the referential integrity of the data in one table to match values in another table.

The FOREIGN KEY CONSTRAINT is a column or list of columns which points to the PRIMARY KEY of another table.

The main purpose of FOREIGN KEY is, only those values will appear which are present in the primary key table.

For each row in the referencing table( the table contains the FOREIGN KEY), the foreign key must match an existing primary key in the referenced table(the table contains the PRIMARY KEY). This enforcement of FOREIGN KEY called the Referential Integrity.

The structure and data type of PRIMARY KEY and FOREIGN KEY must be same.

The values of the FOREIGN KEY columns in each row of the referencing table have to match with the values of the corresponding primary key columns of a row in the referenced table.

Syntax:

CREATE TABLE <table_name>( 
column1    data_type[(size)] ,  
column2    data_type[(size)] ,  
constraint(constraint_name) 
FOREIGN KEY  [column1,column2...] 
REFERENCES [primary_key_table] (column_list_of_primary_key_table) ...);

Parameters:

Name	Description
table_name	The name of the table where data is stored.
column1,column2	Name of the columns of a table.
data_type	Is char, varchar, integer, decimal, date and more.
size	Maximum length of the column of a table.
constraint	Is a key word. This key word is optional.
constraint_name	Is a constraint name defined by user.
primary_key_table	Table where primary key resides.
column_list_of_primary_key_table	List of columns which makes primary key for a table.

Example:

Suppose, we have a table 'agents', that includes all agents data, and we are going to create another table named 'customer1', that includes all customers records. The columns and data types for both the tables have shown bellow.

The constraint here is that all customers must be associated with an agent that is already in the 'agents' table. In this case, an SQL FOREIGN KEY CONSTRAINT should be created with the 'customer1' table which is related to the SQL PRIMARY KEY CONSTRAINT of the 'agents' table.

Now, we can ensure that all customers in the 'customer1' table are related to an agent in the 'agents' table. In other words, the 'customer1' table can not contain information of any agent who is not on the 'agents' table.

agents

Field Name	Data Type	Size	Decimal Places	NULL	Constraint
agent_code	char	6		No	PRIMARY KEY
agent_name	char	40		No
working_area	char	35		Yes
commission	decimal	10	2	Yes
phone_no	char	17		Yes

customer1

Field Name	Data Type	Size	NULL	Constraint
cust_code	char	6	No	PRIMARY KEY
cust_name	char	25	Yes
cust_city	char	25	Yes
agent_code	char	6	Yes	FOREIGN KEY

the following SQL statement can be used :

SQL Code:


-- Creating a table named 'customer1'
CREATE TABLE customer1 (
   -- Column: cust_code with a data type CHAR(6), NOT NULL, and PRIMARY KEY
   cust_code CHAR(6) NOT NULL PRIMARY KEY,
   -- Column: cust_name with a data type CHAR(25)
   cust_name CHAR(25),
   -- Column: cust_city with a data type CHAR(25)
   cust_city CHAR(25),
   -- Column: agent_code with a data type CHAR(6)
    agent_code char(6),
   -- Setting up a foreign key constraint referencing the 'agent_code' column in the 'agents' table
   FOREIGN KEY(agent_code) REFERENCES agents(agent_code)
);

Explanation:

Creating a table named 'customer1': This is a comment line indicating the purpose of the SQL code, which is to create a table named 'customer1'.
CREATE TABLE customer1 (: This line initiates the creation of a new table named 'customer1'.
cust_code CHAR(6) NOT NULL PRIMARY KEY,: This line defines a column named 'cust_code' with a data type CHAR(6). CHAR is a fixed-length character data type, and in this case, it allows up to 6 characters. The column is specified as NOT NULL, meaning it must have a value for each row, and it is designated as the PRIMARY KEY for the table, ensuring uniqueness of values and efficient indexing.
cust_name CHAR(25),: This line defines a column named 'cust_name' with a data type CHAR(25). It can store up to 25 characters. No constraints are specified, so NULL values are allowed for this column.
cust_city CHAR(25),: This line defines a column named 'cust_city' with a data type CHAR(25). It can store up to 25 characters. No constraints are specified, so NULL values are allowed for this column.
agent_code char(6),: This line defines a column named 'agent_code' with a data type CHAR(6). It can store up to 6 characters. No constraints are specified, so NULL values are allowed for this column.
FOREIGN KEY(agent_code) REFERENCES agents(agent_code): This line sets up a foreign key constraint on the 'agent_code' column, referencing the 'agent_code' column in the 'agents' table. This constraint ensures that values in the 'agent_code' column of the 'customer1' table must exist in the 'agent_code' column of the 'agents' table, maintaining referential integrity.

Pictorial representation

SQL CREATE TABLE with FOREIGN KEY in column constraint

In the following topic, we are going to discuss the usage of FOREIGN KEY CONSTRAINT without using the 'foreign key' keyword.

Example:

To create a table which contains the following field names and data types.

Field Name	Data Type	Size	NULL	Constraint
cust_code	char	6	No	PRIMARY KEY
cust_name	char	25	Yes
cust_city	char	25	Yes
agent_code	decimal	6	Yes

The table contains a PRIMARY KEY CONSTRAINT on 'cust_code' and a FOREIGN KEY on 'agent_code' without using the FOREIGN KEY key word.

The 'agent_code' in 'agents' table are unique.

Only those 'agent_code' which are present in 'agents' table will appear in 'mytest' table because reference column is 'agent_code' of 'agents' table.

the following SQL statement can be used :

SQL Code:


-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: cust_code with a data type CHAR(6), NOT NULL, and PRIMARY KEY
    cust_code CHAR(6) NOT NULL PRIMARY KEY,
    
    -- Column: cust_name with a data type CHAR(25)
    cust_name CHAR(25),
    
    -- Column: cust_city with a data type CHAR(25)
    cust_city CHAR(25),
    
    -- Column: agent_code with a data type CHAR(6)
	agent_code  CHAR(6),
	-- Setting up a foreign key constraint referencing the 'agent_code' column in the 'agents' table
    REFERENCES agents(agent_code)
);

Explanation:

This SQL code attempts to create a table named 'mytest' with several columns, including a primary key and a foreign key constraint.

CREATE TABLE mytest (: This line initiates the creation of a new table named 'mytest'.
cust_code CHAR(6) NOT NULL PRIMARY KEY,: This line defines a column named 'cust_code' with a data type CHAR(6). CHAR is a fixed-length character data type, and in this case, it allows up to 6 characters. The column is specified as NOT NULL, meaning it must have a value for each row, and it is designated as the PRIMARY KEY for the table, ensuring uniqueness of values and efficient indexing.
cust_name CHAR(25),: This line defines a column named 'cust_name' with a data type CHAR(25). It can store up to 25 characters. No constraints are specified, so NULL values are allowed for this column.
cust_city CHAR(25),: This line defines a column named 'cust_city' with a data type CHAR(25). It can store up to 25 characters. No constraints are specified, so NULL values are allowed for this column.
agent_code CHAR(6),: This line defines a column named 'agent_code' with a data type CHAR(6). It can store up to 6 characters. No constraints are specified, so NULL values are allowed for this column.
FOREIGN KEY(agent_code) REFERENCES agents(agent_code): This line sets up a foreign key constraint on the 'agent_code' column, referencing the 'agent_code' column in the 'agents' table. This constraint ensures that values in the 'agent_code' column of the 'mytest' table must exist in the 'agent_code' column of the 'agents' table, maintaining referential integrity.

To see the structure of the created table:

SQL Code:

DESCRIBE mytest;

Output:

Sql create table with foreign key in column constraint

SQL CREATE TABLE using FOREIGN KEY CONSTRAINT without specifying PRIMARY KEY column(s)

In the following topic, we are going to discuss, how FOREIGN KEY CONSTRAINT can be used without specifying the primary key column(s).

Example:

To create a table containing the following field names and data types:

Field Name	Data Type	Size	NULL	Constraint
cust_code	char	6	No	PRIMARY KEY
cust_name	char	25	Yes
cust_city	char	25	Yes
agent_code	decimal	6	Yes

The table contains a PRIMARY KEY CONSTRAINT on 'cust_code' and a FOREIGN KEY on 'agent_code' without specifying the PRIMARY KEY column -

The 'agent_code' in 'agents' table are unique.

Only those 'agent_code' which are present in 'agents' table will appear in 'mytest' table because reference column is 'agent_code' of 'agents' table.

the following SQL statement can be used:

SQL Code:


-- Creating a table named 'mytest'
CREATE TABLE mytest (
   -- Column: cust_code with a data type CHAR(6), NOT NULL, and PRIMARY KEY
   cust_code CHAR(6) NOT NULL PRIMARY KEY,
   -- Column: cust_name with a data type CHAR(25)
   cust_name CHAR(25),
   -- Column: cust_city with a data type CHAR(25)
   cust_city CHAR(25),
   -- Column: agent_code with a data type CHAR(6)
   agent_code char(6)
   -- Setting up a foreign key constraint referencing the 'agent_code' column in the 'agents' table
   REFERENCES agents
);

Explanation:

CREATE TABLE mytest (: Initiates the creation of a new table named 'mytest'.
cust_code CHAR(6) NOT NULL PRIMARY KEY,: Defines a column named 'cust_code' with a data type CHAR(6). It is specified as NOT NULL, meaning it must have a value for each row. Additionally, it is designated as the PRIMARY KEY for the table, ensuring uniqueness of values and efficient indexing.
cust_name CHAR(25),: Defines a column named 'cust_name' with a data type CHAR(25). It can store up to 25 characters. No constraints are specified, so NULL values are allowed for this column.
cust_city CHAR(25),: Defines a column named 'cust_city' with a data type CHAR(25). It can store up to 25 characters. No constraints are specified, so NULL values are allowed for this column.
agent_code char(6): Defines a column named 'agent_code' with a data type CHAR(6). It can store up to 6 characters. No constraints are specified, so NULL values are allowed for this column.
REFERENCES agents: This line is attempting to set up a foreign key constraint on the 'agent_code' column, referencing the 'agent_code' column in the 'agents' table. However, it is incomplete. The syntax for the foreign key constraint is not properly defined. The column in the 'agents' table that 'agent_code' is referencing should be specified after the 'REFERENCES' keyword. Also, the constraint name is missing. Typically, you would also define the action to take on update or delete of the referenced row using ON UPDATE and ON DELETE clauses.

To see the structure of the created table:

SQL Code:

DESCRIBE mytest;

Output:

Creating table using foreign key without primary key column list

SQL CREATE TABLE using FOREIGN KEY CONSTRAINT with PRIMARY KEY column list

In the following topic, it is going to be discussed that, how SQL FOREIGN KEY CONSTRAINT is used with primary key column list in a CREATE TABLE statement.

Example:

To create a table which contains the following field names and data types -

Field Name	Data Type	Size	Decimal Places	NULL	Constraint
cust_code	char	6		No	PRIMARY KEY
cust_name	char	40		No
cust_city	char	35		Yes
working_area	char	35		Yes
cust_country	char	20		Yes
grade	decimal	4	0	Yes
opening_amt	decimal	12	2	Yes
receive_amt	decimal	12	2	Yes
payment_amt	decimal	12	2	Yes
outstanding_amt	decimal	12	2	Yes
phone_no	char	17		Yes
agent_code	char	7		Yes	FOREIGN KEY

The table contains a PRIMARY KEY CONSTRAINT on 'cust_code' and a FOREIGN KEY on 'agent_code'.

The 'agent_code' in 'agent1' table are unique,

Only those 'agent_code' which are present in 'agnet1' table will appear in 'mytest' table because reference column is 'agent_code' of 'agnet1' table,

the following SQL statement can be used:

SQL Code:


-- Creating a table named 'mytest'
CREATE TABLE mytest (
   -- Column: cust_code with a data type CHAR(6), NOT NULL, and PRIMARY KEY
   cust_code CHAR(6) NOT NULL PRIMARY KEY,
   -- Column: cust_name with a data type CHAR(40), NOT NULL
   cust_name CHAR(40) NOT NULL,
   -- Column: cust_city with a data type CHAR(35)
   cust_city CHAR(35),
   -- Column: working_area with a data type CHAR(35)
   working_area CHAR(35),
   -- Column: cust_country with a data type CHAR(20)
   cust_country CHAR(20),
   -- Column: grade with a decimal data type (4 digits, 0 decimal places)
   grade DECIMAL(4, 0),
   -- Column: opening_amt with a decimal data type (12 digits, 2 decimal places)
   opening_amt DECIMAL(12, 2),
   -- Column: receive_amt with a decimal data type (12 digits, 2 decimal places)
   receive_amt DECIMAL(12, 2),
   -- Column: payment_amt with a decimal data type (12 digits, 2 decimal places)
   payment_amt DECIMAL(12, 2),
   -- Column: outstanding_amt with a decimal data type (12 digits, 2 decimal places)
   outstanding_amt DECIMAL(12, 2),
   -- Column: phone_no with a data type CHAR(17)
   phone_no CHAR(17),
   -- Column: agent_code with a data type CHAR(6)
   agent_code char(6),
   -- Setting up a foreign key constraint referencing the 'agent_code' column in the 'agent1' table
   FOREIGN KEY(agent_code) REFERENCES agent1(agent_code),
   -- Creating a unique constraint on the combination of cust_code and agent_code columns
   UNIQUE(cust_code, agent_code)
);

Explanation:

CREATE TABLE mytest (: Initiates the creation of a new table named 'mytest'.
Column Definitions:

cust_code CHAR(6) NOT NULL PRIMARY KEY,: Defines a column named 'cust_code' with a data type CHAR(6). It is specified as NOT NULL, meaning it must have a value for each row. Additionally, it is designated as the PRIMARY KEY for the table, ensuring uniqueness of values and efficient indexing.
cust_name CHAR(40) NOT NULL,: Defines a column named 'cust_name' with a data type CHAR(40). It can store up to 40 characters. It is specified as NOT NULL, meaning it must have a value for each row.
cust_city CHAR(35),: Defines a column named 'cust_city' with a data type CHAR(35). It can store up to 35 characters. No constraints are specified, so NULL values are allowed for this column.
working_area CHAR(35), cust_country CHAR(20), grade DECIMAL(4, 0), opening_amt DECIMAL(12, 2), receive_amt DECIMAL(12, 2), payment_amt DECIMAL(12, 2), outstanding_amt DECIMAL(12, 2), phone_no CHAR(17), agent_code char(6): These lines define various columns with their respective data types. Some columns have size constraints and decimal precision specified.

Constraints:

FOREIGN KEY(agent_code) REFERENCES agent1(agent_code),: Sets up a foreign key constraint on the 'agent_code' column, referencing the 'agent_code' column in the 'agent1' table. This constraint ensures referential integrity, meaning that every value in the 'agent_code' column of 'mytest' must exist in the 'agent_code' column of 'agent1'.
UNIQUE(cust_code, agent_code): Creates a unique constraint on the combination of 'cust_code' and 'agent_code' columns. This ensures that each combination of 'cust_code' and 'agent_code' must be unique within the 'mytest' table.

To see the structure of the created table:

SQL Code:

DESCRIBE mytest;

Output:

Creating table using foreign key with primary key column list

SQL CREATE TABLE using FOREIGN KEY on more than one column with PRIMARY KEY column list

In the following topic, we are going to discuss, how SQL FOREIGN KEY CONSTRAINT can be used on more than one columns with primary key column list in a CREATE TABLE statement.

Example:

To create a table which contains the following field names and data types -

Field Name	Data Type	Size	Decimal Places	NULL	Constraint
ord_num	decimal	6		No	PRIMARY KEY
ord_amount	decimal	12	2	Yes
advance_amount	decimal	12	2	No
ord_date	date			No
cust_code	char	6		No	FOREIGN KEY
agent_code	char	6		No	FOREIGN KEY
ord_description	char	60		No

The table contains a PRIMARY KEY CONSTRAINT on 'ord_num' and a FOREIGN KEY in a combination of 'cust_code' and 'agent_code' column.

The 'cust_code' and 'agent_code' combination in 'customer1' table are unique.

Only those 'cust_code' and 'agent_code' combination which are present in 'customer1' table will appear in 'mytest' table because reference columns are 'cust_code' and 'agent_code' combination of 'customer1' table.

the following SQL statement can be used:

SQL Code:


-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: ord_num with a decimal data type (6 digits, 0 decimal places), NOT NULL, and PRIMARY KEY
    ord_num DECIMAL(6) NOT NULL PRIMARY KEY,
    
    -- Column: ord_amount with a decimal data type (12 digits, 2 decimal places)
    ord_amount DECIMAL(12, 2),
    
    -- Column: advance_amount with a decimal data type (12 digits, 2 decimal places), NOT NULL
    advance_amount DECIMAL(12, 2) NOT NULL,
    
    -- Column: ord_date with a date data type, NOT NULL
    ord_date DATE NOT NULL,
    
    -- Column: cust_code with a data type CHAR(6), NOT NULL
    cust_code CHAR(6) NOT NULL,
    
    -- Column: agent_code with a data type CHAR(6), NOT NULL
    agent_code CHAR(6) NOT NULL,
    
    -- Column: ord_description with a data type CHAR(60), NOT NULL
    ord_description CHAR(60) NOT NULL,
    
    -- Setting up a foreign key constraint on cust_code and agent_code columns,
    -- referencing the cust_code and agent_code columns in the 'customer1' table
    FOREIGN KEY(cust_code, agent_code) 
	REFERENCES customer1(cust_code, agent_code)
);

Explanation:

CREATE TABLE mytest (: Initiates the creation of a new table named 'mytest'.
ord_num DECIMAL(6) NOT NULL PRIMARY KEY,: Defines a column named 'ord_num' with a DECIMAL data type, specifying it to have 6 digits and no decimal places. It's designated as NOT NULL and the PRIMARY KEY of the table, ensuring uniqueness and efficient indexing.
ord_amount DECIMAL(12, 2),: Defines a column named 'ord_amount' with a DECIMAL data type, allowing for 12 digits and 2 decimal places.
advance_amount DECIMAL(12, 2) NOT NULL,: Defines a column named 'advance_amount' with a DECIMAL data type, allowing for 12 digits and 2 decimal places. It's designated as NOT NULL.
ord_date DATE NOT NULL,: Defines a column named 'ord_date' with a DATE data type, representing a date. It's designated as NOT NULL.
cust_code CHAR(6) NOT NULL,: Defines a column named 'cust_code' with a CHAR data type, allowing for up to 6 characters. It's designated as NOT NULL.
agent_code CHAR(6) NOT NULL,: Defines a column named 'agent_code' with a CHAR data type, allowing for up to 6 characters. It's designated as NOT NULL.
ord_description CHAR(60) NOT NULL,: Defines a column named 'ord_description' with a CHAR data type, allowing for up to 60 characters. It's designated as NOT NULL.
FOREIGN KEY(cust_code, agent_code) REFERENCES customer1(cust_code, agent_code): Sets up a foreign key constraint on the 'cust_code' and 'agent_code' columns, referencing the 'cust_code' and 'agent_code' columns in the 'customer1' table. This constraint ensures referential integrity between the 'mytest' table and the 'customer1' table.

To see the structure of the created table:

SQL Code:

DESCRIBE mytest;

Output:

Sql create table using foreign key on more columns with primary key column list

SQL CREATE TABLE by referring FOREIGN KEY to own table

In the following topic, we are going to discuss, how SQL FOREIGN KEY CONSTRAINT can be used to refer its own table in a CREATE TABLE statement.

Example:

To create a table which contains the following fields and data types -

Field Name	Data Type	Size	NULL	Constraint
cust_code	char	6	No	PRIMARY KEY
cliant_name	char	40	No	UNIQUE
cliant_city	char	35	No
supp_code	date	6	No

The table contains a PRIMARY KEY on 'cust_code' and a FOREIGN KEY on 'supp_code' where both 'cust_code' and 'supp_code' belong to the 'mytest' table. To achieve the above, the following SQL statement can be used:

SQL Code:


-- Creating a table named 'mytest'
CREATE TABLE mytest (
   -- Column: cust_code with a data type CHAR(6), NOT NULL, and PRIMARY KEY
   cust_code CHAR(6) NOT NULL PRIMARY KEY,
   -- Column: cliant_name with a data type CHAR(40), NOT NULL, and UNIQUE constraint
   cliant_name CHAR(40) NOT NULL UNIQUE,
   -- Column: cliant_city with a data type CHAR(35)
   cliant_city CHAR(35),
   -- Column: supp_code with a data type CHAR(6)
   supp_code char(6)
   -- Setting up a foreign key constraint referencing the 'mytest' table
   REFERENCES mytest(supp_code)
);

Explanation:

CREATE TABLE mytest (: Initiates the creation of a new table named 'mytest'.
cust_code CHAR(6) NOT NULL PRIMARY KEY,: Defines a column named 'cust_code' with a CHAR data type, allowing for up to 6 characters. It is specified as NOT NULL, meaning it must have a value for each row, and it is designated as the PRIMARY KEY for the table, ensuring uniqueness of values and efficient indexing.
cliant_name CHAR(40) NOT NULL UNIQUE,: Defines a column named 'cliant_name' with a CHAR data type, allowing for up to 40 characters. It is specified as NOT NULL, meaning it must have a value for each row, and it has a UNIQUE constraint, ensuring that each value in this column is unique across all rows in the table.
cliant_city CHAR(35),: Defines a column named 'cliant_city' with a CHAR data type, allowing for up to 35 characters. No constraints are specified, so NULL values are allowed for this column.
supp_code char(6): Defines a column named 'supp_code' with a CHAR data type, allowing for up to 6 characters. No constraints are specified, so NULL values are allowed for this column.
REFERENCES mytest(supp_code): This line is attempting to set up a foreign key constraint on the 'supp_code' column, referencing the 'mytest' table. However, the syntax for the foreign key constraint is incorrect. It should reference a different table, not the same table being created. Additionally, the constraint name is missing. The correct syntax should specify the referenced table and column after the 'REFERENCES' keyword, along with the actions on update or delete using ON UPDATE and ON DELETE clauses.

To see the structure of the created table:

SQL Code:

DESCRIBE mytest;

Output:

Sql create table by referring foreign key to own table

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous:Primary Key
Next: Constraints