SQL FOREIGN KEY Constraint

A SQL FOREIGN KEY is a reference key used to link or point two tables together. The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

A foreign key is a single or multiple fields that references a field (most often the primary key) of another table.

The SQL FOREIGN KEY constraint also prevents unknown or invalid data from being inserted into the foreign key field, because it has to be one of the values contained in the primary key table it points to.

For example, Here we have two tables, a AUTHOR table that includes all information about author data, and an BOOKS table that includes all books information as well as author data.

Here the main important thing is, all books(created as foreign key column in the books table) must be connected or associated with a author information that is already in the AUTHOR table. So we create a foreign key on the BOOKS table and have it reference or link the primary key of the AUTHOR table.

Now here the BOOKS table cannot contain data on a author that is not available in the AUTHOR table.

Let's illustrate the foreign key with an example. Look at the following two tables:

The "Author" table:

The "Books" table:

You can drop a foreign key constraint using the following ALTER TABLE syntax:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

When you generated the constraint, the FOREIGN KEY clause defined a CONSTRAINT name, which you may use to delete the foreign key constraint. Otherwise, an internal constraint name will be generated, and you must use it.

Syntax:

In SQL Server (Transact-SQL), the syntax for generating a foreign key with an ALTER TABLE query is:

ALTER TABLE child_table
ADD CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n);

• child_table - The name of the child table that needs to be changed.
• fk_name - The name of the foreign key constraint you want to implement.
• child_col1, child_col2, ... child_col_n - The columns in child_table that will refer to the parent_table's main key.
• parent_table - The name of the parent_table in which the child_table's primary key will be utilised.
• parent_col1, parent_col2, ... parent_col3 - In the parent_table, the columns that make up the main key. This data will be linked to the child_col1, child_col2,... child_col_n columns in the child_table thanks to the foreign key.

Example: Let's look at an example of how to create a foreign key in SQL Server (Transact-SQL) using the ALTER TABLE statement.

ALTER TABLE inventory
ADD CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id);

In this foreign key example, we've created a foreign key on the inventory table called fk_inv_product_id that references the products table based on the product_id field.

We could also create a foreign key with more than one field as in the example below:

ALTER TABLE inventory
ADD CONSTRAINT fk_inv_product
    FOREIGN KEY (product_name, location)
    REFERENCES products (product_name, location);

In this SQL Server example, we have created a foreign key on the inventory table called fk_inv_product that references the products table based on the product_name and location columns.

In relational database design, the FOREIGN KEY constraint is critical. It allows us to link data based on our requirements. It also helps us determine what to do with ON UPDATE and ON DELETE actions done on the rows of the primary table because it introduces certain dependencies between the columns of the primary and foreign tables.

We secure data integrity, accuracy, and compactness by applying the FOREIGN KEY constraint. The foreign table's values must be present in the primary table. We can also establish rules based on the type of relationship, such as one-to-one, many-to-one, or many-to-many.

Foreign keys are a type of constraint provided by SQL Server to protect database integrity. Other data integrity constraint types you may be aware with include Primary Key constraints, Unique constraints, Default constraints, and Check constraints. Each of these constraint kinds has a distinct function. The objective of a foreign key is to ensure that the relationship between a parent table and its child tables is maintained.

To understand the implications of the foreign key, you must first comprehend the various relationships inherent in a relational database. There are three types of connections. There are two tables that can have:

• 1 to 1 refers to a relationship between one record in Table A and one and only one record in Table B.
• When one record in Table A is linked to one or more records in Table B, this is known as 1 to Many. (For example, one consumer may place many orders).
• The condition of many to many is adverse. A Cartesian join is produced when a query is built that connects two unconnected tables. If Table A has 1,000 records and Table B has 10,000, the total number of records will be 1,000 * 10,000 = 10,000,000.

Advantage 1 – Referential Integrity

The benefit of Foreign Keys is Referential Integrity, which ensures that the database maintains referential integrity at all times. This means that for every row with a foreign key in a Child table, there will be a corresponding entry in the Parent table. The cost of CPU when Foreign Key lookups occur during INSERT operations is a downside of employing Foreign Keys.

If a user tries to create a record in the Orders table with a CustomerID that does not exist in the Customers table, the database rejects the entry and displays a warning message.

Will result in the following error…

The FOREIGN KEY constraint "FK Orders Customers" was in conflict with the INSERT statement. The conflict occurred in column 'CustomerID' of table "dbo.Customers" of database "TEST."

The statement has come to an end.

As a result, SQL Server automatically deletes linked child entries when the parent record is destroyed from the Customers table, because the foreign key constraint includes the 'ON DELETE CASCADE' command.

The first Customer record in the Customers database will be deleted by issuing the following command. In addition, all Orders table child records will be erased. SQL Server would have returned an error if the 'ON DELETE CASCADE' option had been removed, because deleting the parent would have resulted in orphan data in the Orders table.

After issuing the previous command, SQL Server now contains the following:

Advantage 2 – Easier Detective Work

One of the intangible benefits of the foreign key restriction is that it makes it much easier for DBAs and database engineers to figure out how the database is set up. You'll see a visible hierarchy demonstrating how the Customers and Orders tables are related when you utilise the 'View Dependencies...' option for either table.

You can continue working down the line to locate any other tables to which the presently selected table is associated with a little more investigation.

Advantage 3 – Better Performance

Better results is another clear benefit of adopting foreign key limitations. SQL Server can simply identify how it will obtain data when using those joins by including information on how tables are joined.

When SQL Server parses the query, it concludes that the following plan will be used to obtain the data. When reading the following result, the database joins the two tables using the primary key CustomerID from the Customers table as the criteria.

Example: The Customers and Orders tables are shown in the table below. Because of the line that connects these two tables, it's obvious that they're related. Because of the key adjacent to the Customers table (parent) and the infinity symbol next to the Orders table, we can see that this is a one to many relationship (child).

Both tables have named the joining column CustomerID in this case. This makes it simple to comprehend and demonstrates a commonality.

ALTER TABLE DBO.< child table>
ADD CONSTRAINT < foreign key name> FOREIGN KEY < child column>
REFERENCES DBO.< parent table>(< parent column>)
{ON [DELETE|UPDATE] CASCADE}

A line item row cannot exist without an order header row, as seen in the following example. The ON DELETE CASCADE option instructs the database engine that if the ORDERNUMBER of the parent ORDER HEADER entry is removed, any LINE ITEM linked to the ORDER HEADER by the deleted ORDERNUMBER should be deleted as well.

ALTER TABLE DBO.LINEITEM
ADD CONSTRAINT FK_LINEITEM_ORDERNUMBER FOREIGN KEY (ORDERNUMBER)
REFERENCES DBO.ORDERHEADER(ORDERNUMBER)
ON DELETE CASCADE

Foreign key constraints in your data model are necessary for obvious reasons.

By preventing data integrity issues in your database, they physically define the business. (For example, without an existing order heading, the database prevents line items from being created.)

They rationally document the business by demonstrating how all data interacts. This enables someone new to your firm to gain a thorough understanding of how the business operates. (For example, each order must be assigned to a valid customer.)

Foreign Keys are built-in to SQL Server and are used to avoid data integrity problems. It's not the job of business logic developers to double-check table relationships.

They can be used by the SQL Server query engine to build exceptionally efficient query plans if they are properly defined and indexed.

Foreign key constraints, unlike primary and unique key constraints, are not automatically indexed by SQL Server. For a few reasons, indexing the column used in your foreign key is a good idea. Without an index, if a user deleted a parent entry from the database, the SQL Server query engine would have to scan the child table referenced in the foreign key to verify data integrity was not compromised.

Cascade delete on a foreign key Either a CREATE TABLE or an ALTER TABLE statement can be used to create it. That is, if a record in the parent table is destroyed, the related records in the child table are also erased automatically. In SQL Server, this is known as a cascade delete.

The result of a UPDATE or DELETE operation on a key value in the parent table that contains matching rows in the child table is determined by the referential action indicated by the FOREIGN KEY clause's ON UPDATE and ON DELETE subclauses.

Using a single Erase statement, delete data from numerous connected tables. However, MySQL has a more effective method for deleting data from child tables called ON DELETE CASCADE referential action for a foreign key, which allows you to delete data from child tables automatically when data from the parent table is deleted.

Referential actions include:

CASCADE: Delete or edit a row in the parent table, and the matching rows in the child table will be deleted or updated as well. ON DELETE CASCADE as well as ON UPDATE CASCADE are both permitted. Do not create multiple ON UPDATE CASCADE clauses in the parent or child tables that act on the same column.

If a FOREIGN KEY clause is defined on both tables in a foreign key relationship, making both tables parents and children, an ON UPDATE CASCADE or ON DELETE CASCADE subclause defined for one FOREIGN KEY clause must also be defined for the other for cascading operations to work. Cascading operations fail with an error if only one FOREIGN KEY clause has an ON UPDATE CASCADE or ON DELETE CASCADE subclause declared.

Note: Cascaded foreign key actions do not activate triggers.

Syntax:

The syntax for creating a foreign key with cascade delete using a CREATE TABLE statement in SQL is:

CREATE TABLE child_table
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
    ON DELETE CASCADE
    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
);

Parameters:

• child_table: The name of the child table that needs to be changed.
• fk_name: The name of the foreign key constraint you want to implement.
• child_col1, child_col2, ... child_col_n: The columns in child_table that will refer to the parent_table's main key.
• parent_table: The name of the parent_table in which the child_table's primary key will be utilised.
• parent_col1, parent_col2, ... parent_col3: In the parent_table, the columns that make up the main key. This data will be linked to the child_col1, child_col2,... child_col_n columns in the child_table thanks to the foreign key.
• ON DELETE CASCADE: When the parent data is removed, the child data is also removed.
• ON UPDATE: Optional. When the parent data is modified, it indicates what happens to the child data. NO ACTION, CASCADE, SET NULL, or SET DEFAULT are the options.
• NO ACTION: ON DELETE or ON UPDATE are used in combination with it. It indicates that when the parent data is deleted or altered, no action is taken with the child data.
• CASCADE: ON DELETE or ON UPDATE are used in combination with it. When the parent data is removed or updated, the child data is also deleted or updated.
• SET NULL: ON DELETE or ON UPDATE are used in combination with it. When the parent data is destroyed or modified, the child data is set to NULL.
• SET DEFAULT: ON DELETE or ON UPDATE are used in combination with it. When the parent data is deleted or altered, the child data is reset to its default settings.

Example 1: MySQL ON DELETE CASCADE example:

Let’s take a look at an example of using MySQL ON DELETE CASCADE.

Suppose that we have two tables:buildings and rooms . In this database model, each building has one or many rooms. However, each room belongs to one only one building. A room would not exist without a building.

The relationship between the buildings and rooms tables is one-to-many (1:N) as illustrated in the following database diagram:

1. MySQL ON DELETE CASCADE - sample tables

When you delete a row from the buildings table, you also want to delete all rows in the rooms table that references to the row in the buildings table. For example, when you delete a row with building no. 2 in the buildings table as the following query:

DELETE FROM buildings 
WHERE building_no = 2;

You also want the rows in the rooms table that refers to building number 2 will be also removed.

The following are steps that demonstrate how the ON DELETE CASCADE referential action works.

Step 1. Create the buildings table:

CREATE TABLE buildings (
    building_no INT PRIMARY KEY AUTO_INCREMENT,
    building_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
);

Step 2. Create the rooms table:

CREATE TABLE rooms (
    room_no INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
        ON DELETE CASCADE
);

Notice that the ON DELETE CASCADE clause at the end of the foreign key constraint definition.

Step 3. Insert rows into the buildings table:

INSERT INTO buildings(building_name,address)
VALUES('ACME Headquaters','3950 North 1st Street CA 95134'),
      ('ACME Sales','5000 North 1st Street CA 95134');

Step 4. Query data from the buildings table:

SELECT * FROM buildings

We have two rows in the buildings table.

Step 5. Insert rows into the rooms table:

INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
      ('War Room',1),
      ('Office of CEO',1),
      ('Marketing',2),
      ('Showroom',2);

Step 6. Query data from the rooms table:

SELECT * FROM rooms;

We have three rooms that belong to building no 1 and two rooms that belong to the building no 2.

Step 7. Delete the building with building no. 2:

DELETE FROM buildings 
WHERE building_no = 2;

Step 8. Query data from rooms table:

SELECT * FROM rooms;

MySQL ON DELETE CASCADE - rooms table after delete

As you can see, all the rows that reference to building_no 2 were automatically deleted.

Notice that ON DELETE CASCADE works only with tables with the storage engines that support foreign keys e.g., InnoDB.

Example 2: Let's look at an example of how to create a foreign key with cascade delete in SQL Server (Transact-SQL) using the CREATE TABLE statement.

CREATE TABLE products
( product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  category VARCHAR(25)
);

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE CASCADE
);

We've made our parent table the products table in this foreign key example. The product_id field serves as the primary key in the products_table.

Then, as the child table in this foreign key with cascade delete example, we built a second table called inventory. The Construct TABLE statement was used to create the fk_inv_product id foreign key on the inventory table. The foreign key makes a link between the inventory table's product_id column and the products table's product_id column.

The ON DELETE CASCADE clause has been defined for this foreign key, which instructs SQL Server to delete the relevant records in the child table when the data in the parent table is erased. If a product id value is removed from the products table, any associated records in the inventory table that use that product id will be removed as well.

To change the datatype of a column in the current table, use the SQL Server ALTER TABLE ALTER COLUMN statement.

Syntax:

The syntax to change the datatype of the column is the following.

ALTER TABLE [tbl_name] ALTER COLUMN [col_name_1] TYPE [DATA_TYPE], 
ALTER COLUMN [col_name_2] TYPE [data_type], 
ALTER COLUMN [col_name_3] TYPE [data_type]

In the syntax,

• Tbl_name: Enter the name of the table that includes the column you want to modify.
• Col_name: Enter the name of the column whose datatype you want to modify. After the ALTER COLUMN keyword, the col name must be supplied. Multiple columns' data types can be changed.
• Datatype: Specify the column's new datatype and length. After the TYPE keyword, the datatype must be given.

SQL Server allows you to perform the following changes to an existing column of a table:

ALTER TABLE table_name 
ALTER COLUMN column_name new_data_type(size);

The new data type must be compatible with the old one; otherwise, if the column contains data and the conversion fails, you will receive a conversion error.

Example 1: SQL query to change the datatype of one column:

The code to create the table is the following.

create table tblmovies
    (
    movie_id int,
    Movie_Title varchar(500),
    Movie_director TEXT,
    Movie_Producer TEXT,
    duraion int,
    Certificate varchar(5),	
    rent numeric(10,2)
    )

Now, let us understand the concept using a few examples.

The data type of the movie id column should be changed from int4 to int8. To alter the datatype, run the following query.

ALTER TABLE tblmovies ALTER COLUMN movie_id TYPE BIGINT

Run the following query to verify the changes:

SELECT table_catalog,
    table_name, 
    column_name, 
    udt_name,
    character_maximum_length 
 FROM 
    information_schema.columns
 WHERE 
    table_name = 'tblmovies';

As you can see, the datatype of the movie_id column has been changed to int8.

Example 2: SQL query to change the datatype of multiple columns:

We can change the datatype of multiple columns of a table. In our example, we want to change the column type of the movie_title and movie_director. The new datatype of the movie_title columns is TEXT, and the new datatype of movie_producer is varchar(2000).

ALTER TABLE tblmovies ALTER COLUMN movie_title TYPE text, ALTER COLUMN movie_producer TYPE varchar(2000);

Run the following query to verify the changes:

SELECT table_catalog,
    table_name, 
    column_name, 
    udt_name,
    character_maximum_length 
 FROM 
    information_schema.columns
 WHERE 
    table_name = 'tblmovies';

As you can see, the datatype of the movie_title columns is TEXT, and the datatype of movie_producer is varchar(2000).

Example 3: First, create a new table with one column whose data type is INT:

CREATE TABLE t1 (c INT);

Second, insert some rows into the table:

INSERT INTO t1
VALUES
(1),
(2),
(3);

Second, modify the data type of the column from INT to VARCHAR:

ALTER TABLE t1 ALTER COLUMN c VARCHAR (2);

Third, insert a new row with a character string data:

INSERT INTO t1
VALUES ('@');

Fourth, modify the data type of the column from VARCHAR back to INT:

ALTER TABLE t1 ALTER COLUMN c INT;

SQL Server issued the following error:

Example 4: Change the size of a column:

The following statement creates a new table with one column whose data type is VARCHAR(10):

CREATE TABLE t2 (c VARCHAR(10));

Let’s insert some sample data into the t2 table:

INSERT INTO t2
VALUES
    ('SQL Server'),
    ('Modify'),
    ('Column')

You can increase the size of the column as follows:

ALTER TABLE t2 ALTER COLUMN c VARCHAR (50);

When you reduce the size of a column, SQL Server examines the existing data to see if it can be converted to the new size. SQL Server stops the statement and issues an error message if the conversion fails.

For example, if you decrease the size of column c to 5 characters:

ALTER TABLE t2 ALTER COLUMN c VARCHAR (5);

SQL Server issued the following error:

Example 5: Add a NOT NULL constraint to a nullable column:

The following statement creates a new table with a nullable column:

CREATE TABLE t3 (c VARCHAR(50));

The following statement inserts some rows into the table:

INSERT INTO t3
VALUES
('Nullable column'),
(NULL);

If you want to add the NOT NULL constraint to the column c, you must update NULL to non-null first for example:

UPDATE t3
SET c = ''
WHERE
c IS NULL;

And then add the NOT NULL constraint:

ALTER TABLE t3 ALTER COLUMN c VARCHAR (20) NOT NULL;

Use the SQL Server ALTER TABLE ALTER COLUMN to modify some properties of an existing column.

A foreign key constraint in an existing table can be removed with the ALTER TABLE DROP CONSTRAINT command.

After you've generated a foreign key, you might want to remove it from the table.

Syntax: Delete Foreign Key

ALTER TABLE < table_name>
DROP CONSTRAINT < foreignkey_name>;

Parameters or Arguments:

• table_name: The name of the table where the foreign key has been created.
• fk_name: The name of the foreign key that you wish to remove.

Example 1: We want to remove the foreign key named fk_student_city_id from the table student.

ALTER TABLE student
DROP CONSTRAINT fk_student_city_id;

Discussion:

To drop a foreign key from a table, use the ALTER TABLE clause with the name of the table (in our example, student) followed by the clause DROP CONSTRAINT with the name of the foreign key constraint. In our example, the name of this constraint is fk_student_city_id.

Example 2: The following deletes the foreign key on the Employee table.

ALTER TABLE Employee   
DROP CONSTRAINT FK_Employee_Department

Example 3: Let's look at an example of how to drop a foreign key in SQL Server :

For example, if you had created a foreign key as follows:

CREATE TABLE products
( product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  category VARCHAR(25)
);

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
);

We've made our parent table the products table in this foreign key example. The product id field serves as the primary key in the products table.

Then, as the child table in this foreign key example, we've established a second table called inventory. On the inventory database, we created a foreign key called fk_inv_product id using the CREATE TABLE statement. The product id column in the inventory table and the product_id column in the products table are linked by the foreign key.

Then, if we wanted to remove the foreign key fk inv product id, we could use the command below:

ALTER TABLE inventory
DROP CONSTRAINT fk_inv_product_id;

This foreign key example would use the ALTER TABLE statement to drop the constraint called fk_inv_product_id from the inventory table.

If any column in the foreign key is null, the foreign key as a whole is regarded as null. The INSERT succeeds if all foreign keys containing the column are null (as long as there are no unique index violations).

Each non-null value you enter in a foreign key column must match a value in the parent table's corresponding parent key.

To establish two foreign keys in the sample application project table (PROJECT), do the following changes:

• The department number (DEPTNO) has a foreign key that refers to the department table.
• The employee table is referenced through a foreign key on the employee number (RESPEMP).

ALTER TABLE CORPDATA.PROJECT ADD CONSTRAINT RESP_DEPT_EXISTS
    FOREIGN KEY (DEPTNO)
    REFERENCES CORPDATA.DEPARTMENT
    ON DELETE RESTRICT
 
ALTER TABLE CORPDATA.PROJECT ADD CONSTRAINT RESP_EMP_EXISTS
    FOREIGN KEY (RESPEMP)
    REFERENCES CORPDATA.EMPLOYEE
    ON DELETE RESTRICT

The REFERENCES clause does not provide the columns of the parent table. As long as the referred table has a primary key or an acceptable unique key that may be used as the parent key, the columns do not need to be specified.

Every row in the PROJECT table must have a DEPTNO value that matches one of the DEPTNO values in the department table. (A null value is not permitted because DEPTNO is declared as NOT NULL in the project table.) The row must also have a RESPEMP value that is either equal to or null from the employee table's EMPNO column.

The following INSERT statement fails because there is no matching DEPTNO value ('A01') in the DEPARTMENT table.

INSERT INTO  CORPDATA.PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP)
VALUES  ('AD3120', 'BENEFITS ADMIN', 'A01', '000010')

Likewise, the following INSERT statement is unsuccessful because there is no EMPNO value of '000011' in the EMPLOYEE table.

INSERT INTO CORPDATA.PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP)
VALUES ('AD3130', 'BILLING', 'D21', '000011')

The following INSERT statement completes successfully because there is a matching DEPTNO value of 'E01' in the DEPARTMENT table and a matching EMPNO value of '000010' in the EMPLOYEE table.

INSERT INTO CORPDATA.PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP)
VALUES ('AD3120', 'BENEFITS ADMIN', 'E01', '000010')

In a relational database, foreign keys are used to establish relationships between tables. Vary based on the type of data you wish to store, a table can have one-to-one, one-to-many, or many-to-many relationships with another table.

A foreign key occurs when a non-prime attribute column in one table refers the primary key and has the same column as the table's prime attribute column.

A foreign key is a key that connects two tables. This is sometimes referred to as a reference key.

A Foreign Key is a column or a set of columns whose values correspond to those of a Primary Key in another table.

The Primary Key in one table corresponds to a Foreign Key in the other table in this relationship.

If a primary key is defined on any field(s) in a table, no two records can have the same value for that field (s).

A one-to-one relationship is straightforward and hardly utilised. Each record in one table corresponds to exactly one record in another.

For instance, you can record the user's name, id, and gender in one table (User table), and their addresses in another table (Address table). Each row in the User table corresponds to a row in the Address table. In contrast, each address in the Address table will only be associated with one record in the Address table.

One-to-many and many-to-many relations are far more frequent.

Imagine a case in which you need to keep track of an organization's personnel, departments, insurance firms, and office addresses.

We presume that an employee can only be a part of one department and can only be a member of one insurance company. Departments and insurance businesses, on the other hand, can have a large number of staff. There would be a one-to-many link between the Department and Employee tables. Similarly, there would be a one-to-many relationship between the Insurance and Employee tables.

Additionally, the company may have many offices, and a person may work in more than one of them. Furthermore, an office can have a large number of staff. The Office and Employee tables would have a many-to-many relationship in this situation.

A look-up table that connects two tables in a many-to-many relationship is required to execute the many-to-many relationship. The look-up table has a one-to-many link with the original tables.

Syntax:

column_name(non_prime) data_type REFERENCES table_name(column_name(prime)

Example 1: Let’s consider creating a many-to-many relationship between the Employee and Office tables. You would create a new look-up table Employee_Office. Then you create a one-to-many relationship between the Employee and Employee_Office tables and the Office and Employee_Office tables.

1. Adding Multiple Foreign Keys with SQL Server

The following diagram shows the database schema that you are going to implement for our simple example:

database has five tables: Employee, Insurance, Department, Office, and Employee_Office.

Here you can see that the database has five tables: Employee, Insurance, Department, Office, and Employee_Office.

There is a one-to-many link between the Department and Employee tables. Similarly, there is a one-to-many relationship between the Insurance and Employee tables. A many-to-many link exists between the Office and Employee tables, which is implemented utilising two one-to-many relationships using the Employee_Office lookup table.

Dep_Id and Insur_Id are foreign keys in the Employee database that refer to the primary keys (Id) of the Department and Insurance tables, respectively.

To begin, create tables with no foreign keys. You'll get an error if you try to build tables with foreign keys that refer to tables that haven't been created yet.

The Department, Organization, and Office tables in our database schema do not contain any foreign keys. The script below creates a fictional database called Organization and populates it with three tables: Insurance, Department, and Office.

CREATE DATABASE Organization

USE Organization
CREATE TABLE Department
(
Id INT PRIMARY KEY,
Name VARCHAR (50) NOT NULL
)

USE Organization
CREATE TABLE Insurance
(
Id INT PRIMARY KEY,
Name VARCHAR (50) NOT NULL
)

USE Organization
CREATE TABLE Office
(
Id INT PRIMARY KEY,
Name VARCHAR (50) NOT NULL
)

In SQL Server, the FOREIGN KEY REFERENCES constraint can be used to create a foreign key relationship. Give the table a name. Then enter the column name for the foreign key in parenthesis to refer to it.

The following script generates the Employee table, which contains foreign keys Dep Id and Insur Id, which correspond to the Department and Insurance tables' Id columns, respectively.

USE Organization
CREATE TABLE Employee
(
Id INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR (50) NOT NULL,
Age INT,
Gender VARCHAR (50),
Dep_Id int FOREIGN KEY REFERENCES Department(Id),
Insur_Id int FOREIGN KEY REFERENCES Insurance(Id)
)

Finally, the following script creates the Employee_Office table with two foreign keys Emp_Id and Office_Id.

USE Organization
CREATE TABLE Employee_Office
(
Id INT PRIMARY KEY IDENTITY(1,1),
Emp_Id int FOREIGN KEY REFERENCES Employee(Id),
Office_Id int FOREIGN KEY REFERENCES Office(Id)
)

2. Inserting Records in Tables with Multiple Foreign Keys

Create equivalent entries in the tables that are referenced by foreign keys in the original tables before inserting records into tables with multiple foreign keys.

In practise, we must first construct comparable data in the Department and Insurance tables before inserting records into the Employee table. Because the Employee table contains foreign keys to the Department and Insurance tables, this is the case.

First, we try to populate the Employee database without referring the Department and Employee tables using foreign keys.

INSERT INTO Employee
VALUES ('James', 10, 'Male'),
('Sara', 7, 'Female')

You will see the following error. It occurs because the number of values specified doesn’t match the number of columns in the Employee table.

error that occurs because the number of values specified doesn’t match the number of columns in the Employee tabl

Let’s try to add some dummy values for the Dep_Id and Insur_Id columns (foreign keys):

INSERT INTO Employee
VALUES ('James', 10, 'Male', 2, 2),
('Sara', 7, 'Female', 1, 1)

Because records with ids 2 and 1 do not exist in the Department and Insurance tables, you will receive the following error.

The error appears because records with ids 2 and 1 are missing from the Department and Insurance tables, respectively.

Let's now populate the Department, Insurance, and Office tables with data:

INSERT INTO Department
VALUES (1, 'Finance'),
(2, 'HR')

INSERT INTO Insurance
VALUES (1, 'Company A'),
(2, 'Company B')

INSERT INTO Office
VALUES (1, 'Paris'),
(2, 'London')

Because the Department and Insurance tables now have records with Ids 2 and 1, you may now insert records with equivalent foreign key values into the Employee tables, as demonstrated below:

INSERT INTO Employee
VALUES ('James', 10, 'Male', 2, 2),
('Sara', 7, 'Female', 1, 1)

Insert some records into the Employee_Office table. But before that, let’s see the Id column values in the Employee table:

SELECT * FROM Employee

Id column values in the Employee table:

The Employee column contains records with Id values of 2 and 3. You can insert records into the Employee_Office table where the Emp_Id column contains 2 or 3, and the Office_Id column contains 1 or 2.

INSERT INTO Employee_Office
VALUES (2, 1),
(2, 2),
(3,2)

3. Selecting Records from Tables with Multiple Foreign Keys

The Name and Gender columns from the Employee table, as well as the Name columns from the Department and Insurance tables, are returned by the following script. Because there are two foreign keys in the Employee database, you must use two LEFT JOIN statements:

SELECT 
Employee.Name AS Employee_Name, 
Employee.Gender, 
Department.Name as Department_Name, 
Insurance.Name as Insurance
FROM Employee
LEFT JOIN Department ON Employee.Dep_Id  =  Department.Id
LEFT JOIN Insurance  ON Employee.Insur_Id = Insurance.Id

Returned values of the Name and Gender columns from the Employee table and the Name columns from the Department and Insurance tables:

Using two LEFT JOIN statements on the lookup table Employee_Office, you may select values for the Name and Gender columns from the Employee dataset, as well as the Name field from the Office table.

SELECT 
Employee.Name AS Employee_Name, 
Employee.Gender, 
Office.Name as Office_Name
FROM Employee
LEFT JOIN Employee_Office ON Employee.Id  =  Employee_Office.Emp_Id
LEFT JOIN Office  ON Office.Id = Employee_Office.Office_Id

returned values of the Name and Gender columns from the Employee table and the Name column from the Office table.

4. DELETING Records from Tables with Multiple Foreign Keys

Tables with multiple foreign keys can have records deleted. However, make sure the table isn't linked to another column by a foreign key.

For example, records from the Department table that are referenced by the Emp_Id foreign key in the Employee table should not be deleted. Here's an illustration:

DELETE FROM Department WHERE Id = 1

Because the Emp_Id column in the Employee table refers to the record with Id 1 in the Department table, You cannot delete the entry with Id 1 in the Department table because it is referenced by the Emp_Id column in the Employee table, as stated in the previous error. To begin, delete all records in the Employee table with an Emp_Id of 1.

The Employee table contains only 1 such record (with the Id value of 3). Let’s try to delete that record with the following query:

DELETE FROM Employee WHERE Id = 3

You will see the error which occurs because Employee_Office contains a record where the Emp_Id foreign key column contains 3 records. Hence you will need to remove that record first:

error which occurs because Employee_Office contains a record where the Emp_Id foreign key column contains 3 records.

The following script removes the record with the Emp_Id value of 3 from the Employee_Office table.

DELETE FROM Employee_Office WHERE Emp_Id = 3

Finally, the following script removes the record with Id values of 1 from the Employee and Department columns.

DELETE FROM Employee WHERE Id = 1
DELETE FROM Department WHERE Id = 1

Example 2:

Step 1: Creating a Database

We use the below command to create a database named GeeksforGeeks:

CREATE DATABASE Format

Step 2: Using the Database

To use the Format database use the below command:

USE Format

Step 3: Creating 3 tables. The table student_details contains two foreign keys that reference the tables student_branch_details and student_address.

CREATE TABLE student_details(
  stu_id VARCHAR(8) NOT NULL PRIMARY KEY,
  stu_name VARCHAR(20),
  stu_branch VARCHAR(20) FOREIGN KEY REFERENCES student_branch_details(stu_branch),
  stu_pin_code VARCHAR(6) FOREIGN KEY REFERENCES student_address(stu_pin_code)
  );

CREATE TABLE student_branch_details(
  stu_branch VARCHAR(20) PRIMARY KEY,
  subjects INT,
  credits INT
);

CREATE TABLE student_address(
  stu_pin_code VARCHAR(6) PRIMARY KEY,
  stu_state VARCHAR(20),
  student_city VARCHAR(20)
);

In the tables area of the object explorer on the left side of the UI, you can verify the number and kind of keys.

Step 4: Inserting data into the Table

Inserting rows into student_branch_details and student_address tables using the following SQL query:

INSERT INTO student_branch_details VALUES
  ('E.C.E',46,170),
  ('E.E.E',47,178),
  ('C.S.E',44,160)

INSERT INTO student_address VALUES
  ('555555', 'xyz','abc'),
  ('666666', 'yyy','aaa'),
  ('777777','zzz','bbb'),
  ('888888','www','ccc'),
  ('999999','vvv','ddd')

Inserting rows into student_details

INSERT INTO student_details VALUES
('1940001','PRATHAM','E.C.E','555555'),
('1940002','ASHOK','C.S.E','666666'),
('1940003','PAVAN KUMAR','C.S.E','777777'),
('1940004','SANTHOSH','E.C.E','888888'),
('1940005','THAMAN','E.C.E','999999'),
('1940006','HARSH','E.E.E','888888')

Step 5: Verifying the inserted data:

Viewing the tables student_details,student_branch_details,student_address after inserting rows by using the following SQL query:

SELECT * FROM student_details

SELECT * FROM student_branch_details

SELECT * FROM student_address

Example 3: Consider the structure of the following two tables.

CUSTOMERS table

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

ORDERS table

CREATE TABLE ORDERS (
   ID          INT        NOT NULL,
   DATE        DATETIME, 
   CUSTOMER_ID INT references CUSTOMERS(ID),
   AMOUNT     double,
   PRIMARY KEY (ID)
);

Use the syntax for defining a foreign key by changing a table if the ORDERS table has already been established but the foreign key has not yet been set.

ALTER TABLE ORDERS 
ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);

A foreign key occurs when a non-prime attribute column in one table refers the primary key and has the same column as the table's prime attribute column. It establishes a link between the two tables, which aids in the tables' normalisation. Depending on the situation, a table can have numerous foreign keys.

Let's look at how to make a table with numerous foreign keys in MSSQL in this tutorial.

Syntax:

column_name(non_prime) data_type REFERENCES table_name(column_name(prime)

1. Creating a Database

We use the below command to create a database named Tabledetails:

CREATE DATABASE Tabledeatails

2. Using the Database

To use the Tabledetails database use the below command:

USE Tabledetails

3. Creating 3 tables

The table student_details contains two foreign keys that reference the tables student_branch_details and student_address.

CREATE TABLE student_details(
  stu_id VARCHAR(8) NOT NULL PRIMARY KEY,
  stu_name VARCHAR(20),
  stu_branch VARCHAR(20) FOREIGN KEY REFERENCES student_branch_details(stu_branch),
  stu_pin_code VARCHAR(6) FOREIGN KEY REFERENCES student_address(stu_pin_code)
  );

CREATE TABLE student_branch_details(
  stu_branch VARCHAR(20) PRIMARY KEY,
  subjects INT,
  credits INT
);

CREATE TABLE student_address(
  stu_pin_code VARCHAR(6) PRIMARY KEY,
  stu_state VARCHAR(20),
  student_city VARCHAR(20)
);

4. Inserting data into the Table

Inserting rows into student_branch_details and student_address tables using the following SQL query:

INSERT INTO student_branch_details VALUES
  ('E.C.E',46,170),
  ('E.E.E',47,178),
  ('C.S.E',44,160)

INSERT INTO student_address VALUES
  ('555555', 'xyz','abc'),
  ('666666', 'yyy','aaa'),
  ('777777','zzz','bbb'),
  ('888888','www','ccc'),
  ('999999','vvv','ddd')

Inserting rows into student_details

INSERT INTO student_details VALUES
('1940001','PRATHAM','E.C.E','555555'),
('1940002','ASHOK','C.S.E','666666'),
('1940003','PAVAN KUMAR','C.S.E','777777'),
('1940004','SANTHOSH','E.C.E','888888'),
('1940005','THAMAN','E.C.E','999999'),
('1940006','HARSH','E.E.E','888888')

5. Verifying the inserted data

Viewing the tables student_details,student_branch_details,student_address after inserting rows by using the following SQL query:

SELECT * FROM student_details

SELECT * FROM student_branch_details

SELECT * FROM student_address

Reference to a primary or unique key can be used to generate foreign key limitations. These are used to keep similar data in multiple tables in order. We must examine the influence on related values in the child table when performing update and delete operations on data in the parent table (referenced table with primary key). For regulating the effect of updates and deletes on child table values, SQL Server supports several rules.

The relational integrity of data in related tables is ensured by foreign key constraints. A NULL value for a foreign key indicates that a record has no parent record. However, if a value exists, it is almost certain to be related with a value in a parent table. There may be various criteria for the effect on associated values in child tables when performing update or delete actions on parent tables.

The same rule does not have to be applied to both update and remove actions.

On a same FK constraint, there may be distinct rules for each update and delete operation. Here's a rundown of the effects for update and delete operations before we get started with the demo:

Set Default The default value supplied in the column definition would be applied to associated values in the child table. In addition, the primary key column should have a default value. Otherwise, the FK relation's basic criterion would be violated, and the update process would fail. This rule will not work if there is no default value in the foreign key column.

The default value supplied in the column definition would be applied to associated values in the child table. In addition, the primary key column should have a default value. Otherwise, the FK relation's basic criterion would be broken, and the delete operation would fail. This rule will not work if there is no default value in the foreign key column.

Example: The following demonstrates each of these options.

Create and populate tables having FK relation

Now let's create a couple of tables to resemble a foreign key relationship.

Script 1: Create sample tables

-- Use required database
Use AdventureWorks
GO

-- Create child table
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'EmpEducation' AND [type] = 'U')
DROP TABLE EmpEducation
CREATE TABLE EmpEducation
(
EduID SMALLINT IDENTITY(1,1) PRIMARY KEY,
empno SMALLINT NULL DEFAULT 100,
DegreeTitle VARCHAR(50)
)
GO

-- Create parent table
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'employees' AND [type] = 'U')
DROP TABLE employees
CREATE TABLE employees
(
empno SMALLINT PRIMARY KEY ,
EmpName VARCHAR(70)
)
GO

Either the SSMS GUI or T-SQL can be used to construct the foreign key relationship. Explicit rules for update/delete actions can be provided. If no action is provided, however, the default rule is No Action. By recreating the FK relation, the rule can be modified to any other option at any moment. Let's use T-SQL to construct a foreign key with the default parameters.

Script 2: Create FK relationship

-- Create FK relationship
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'FK_EmpEducation_Employees' AND [type] = 'F')
ALTER TABLE EmpEducation
DROP Constraint FK_EmpEducation_Employees
GO

ALTER TABLE EmpEducation
ADD CONSTRAINT [FK_EmpEducation_Employees]
FOREIGN KEY (empno)REFERENCES employees(empno)
GO

Now populate the tables with sample data.

Script 3: Populate the tables with sample data

-- Insert records in parent table
INSERT INTO employees
SELECT 1, 'Atif' UNION ALL
SELECT 2, 'Shehzad' UNION ALL
SELECT 3, 'khurram' UNION ALL
SELECT 4, 'Ahmed' UNION ALL
SELECT 5, 'Uzair'
GO

-- Insert records in parent table
INSERT INTO EmpEducation
SELECT 1, 'MS' UNION ALL
SELECT 2, 'MBA' UNION ALL
SELECT 1, 'BS' UNION ALL
SELECT 2, 'MS' UNION ALL
SELECT 3, 'BS'
GO

We can now examine the relationship in conjunction with the rules offered in SSMS. To access the relationships frame in SSMS, right-click on the FK for table EmpEducation and select change.

NO ACTION option

SQL Server would not enable an update or delete operation on referenced values of the primary key table for our data if the default specification (No Action) was used. Because we didn't specify a foreign key specification, the default No Action is used. As a result, no changes or deletions to values that are referenced in the child table should be permitted.

Let's verify the effect of the No Action rule.

Script 4: Update and delete with 'No Action' rule

-- Try to update referenced PK
UPDATE Employees
SET empno = 100 WHERE empno = 1
GO

-- Try to delete record with referenced PK
DELETE FROM Employees
WHERE empno = 2
GO

As a result of the above script, the following error messages were created, and the update and delete operations were not completed.

The UPDATE statement conflicted with the REFERENCE constraint "FK_EmpEducation_Employees". The conflict occurred in database "AdventureWorks", table "dbo.EmpEducation", column 'empno'.

The DELETE statement conflicted with the REFERENCE constraint "FK_EmpEducation_Employees". The conflict occurred in database "AdventureWorks", table "dbo.EmpEducation", column 'empno'.

CASCADE option

Let's now alter the default action (No Action) to Cascade. Use this T-SQL code or select the CASCADE rule from the SSMS GUI as seen in the preceding screenshot.

The following script adds "ON DELETE CASCADE ON UPDATE CASCADE"

Script 5: Create FK relationship with CASCADE

-- Create FK relationship
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'FK_EmpEducation_Employees' AND [type] = 'F')
ALTER TABLE EmpEducation
DROP Constraint FK_EmpEducation_Employees
GO

ALTER TABLE EmpEducation
ADD CONSTRAINT [FK_EmpEducation_Employees]
FOREIGN KEY (empno)REFERENCES employees(empno)
ON DELETE CASCADE ON UPDATE CASCADE
GO

Run script #4 again to verify the result produced with the cascade option.

select the cascade rule from ssms gui or use this t-sql code

SET NULL option

To utilize the SET NULL rule for update/delete operations the foreign key column should allow NULL values otherwise the SET NULL specification would fail by generating an error message.

Run script # 1 - to recreate the objects

Run the following script which adds "ON DELETE SET NULL ON UPDATE SET NULL"

Script 6: Create FK relationship with SET NULL

-- Create FK relationship
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'FK_EmpEducation_Employees' AND [type] = 'F')
ALTER TABLE EmpEducation
DROP Constraint FK_EmpEducation_Employees
GO

ALTER TABLE EmpEducation
ADD CONSTRAINT [FK_EmpEducation_Employees]
FOREIGN KEY (empno)REFERENCES employees(empno)
ON DELETE SET NULL ON UPDATE SET NULL
GO

Run script # 3 to create the test data

Run script # 4 to verify the functionality

The data in child table (EmpEducation) shows that as a result of the update and delete operations foreign key values are set to NULL as shown below.

utilize the set null rule

SET DEFAULT option

The default value for the foreign key column should be present when using the SET DEFAULT rule for update/delete actions. Otherwise, the SET DEFAULT specification would fail, resulting in an error message. Because the default value for our foreign key column is 100, we can proceed with the procedures below.

Run script # 1 - to recreate the objects

Run the following script which adds "ON DELETE SET DEFAULT ON UPDATE SET DEFAULT"

Script 7: Create FK relationship with SET DEFAULT

-- Create FK relationship
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'FK_EmpEducation_Employees' AND [type] = 'F')
ALTER TABLE EmpEducation
DROP Constraint FK_EmpEducation_Employees
GO

ALTER TABLE EmpEducation
ADD CONSTRAINT [FK_EmpEducation_Employees]
FOREIGN KEY (empno)REFERENCES employees(empno)
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT
GO

Run script # 3 to create the test data

Run script # 4 to verify the functionality

We have specified a default value of 100 for empno in the child table. Script 4 would change the corresponding four values to 100 as shown below.

Foreign keys, which allow related data to be cross-referenced across tables, and foreign key constraints, which assist keep related data consistent, are both supported by MySQL.

A foreign key relationship consists of a parent table containing the initial column values and a child table containing column values that relate to the parent column values. On the child table, a foreign key constraint is defined.

In SQL, you can use the CREATE TABLE and ALTER TABLE statements to add foreign key constraints.

Foreign keys aid in the formation of database associations and the preservation of referential integrity.

Syntax: Adding Foreign Key Constraints

The essential syntax for a defining a foreign key constraint in a CREATE TABLE or ALTER TABLE statement includes the following:

ALTER TABLE tbl_name
   ADD[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:

RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

It is possible for the foreign key to be self-referential (referring to the same table). Keep in mind to construct an index on the column(s) referenced by the foreign key before adding a foreign key constraint to a table using ALTER TABLE.

Example 1: MySQL Alter Table Column

parent_table is the table to which your foreign_key references, followed by list of column names in that table.

Using CREATE TABLE

CREATE TABLE table_name(
    column_name column_description,
    CONSTRAINT constraint_name
    FOREIGN KEY (foreign_key_name,...) 
        REFERENCES parent_table(column_name,...)
)

In the above query, table_name is the table where you want to add foreign key. constraint_name is the name of the foreign key constraint. foreign_key_name, … is the list of foreign key columns.

parent_table is the table to which your foreign_key references, followed by list of column names in that table.

Please keep in mind that in ALTER TABLE, you must use the ADD CONSTRAINT keyword, whereas in CREATE TABLE, you must just use the CONSTRAINT keyword.

Example 2: Foreign Key Constraint Examples

This simple example relates parent and child tables through a single-column foreign key:

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

This is a more complex example in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customer table:

CREATE TABLE product (
    category INT NOT NULL, id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)
)   ENGINE=INNODB;

CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
)   ENGINE=INNODB;

CREATE TABLE product_order (
    no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,

    PRIMARY KEY(no),
    INDEX (product_category, product_id),
    INDEX (customer_id),

    FOREIGN KEY (product_category, product_id)
      REFERENCES product(category, id)
      ON UPDATE CASCADE ON DELETE RESTRICT,

    FOREIGN KEY (customer_id)
      REFERENCES customer(id)
)   ENGINE=INNODB;

• A foreign key is a field in one database table that serves as the main key in another.
• The foreign key's function is to verify that the data is referentially correct.
• If the CONSTRAINT symbol is specified, it is used.
• A constraint name name is created automatically if the CONSTRAINT symbol clause is not defined, or if a symbol is not included after the CONSTRAINT keyword. Both InnoDB and NDB storage engines would use the FOREIGN_KEY index name if described. The FOREIGN_KEY index name is omitted in MySQL 8.0.16 and higher.
• The CONSTRAINT symbol value, if defined, must be unique in the database. A duplicate symbol results in an error similar to: ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121).
• NDB Cluster stores foreign names using the same lettercase with which they are created. when processing SELECT and other SQL statements, NDB compared the names of foreign keys in such statements with the names as stored in a case-sensitive fashion when lower_case_table_names was equal to 0. In NDB 8.0.20 and later, this value no longer has any effect on how such comparisons are made, and they are always done without regard to lettercase. (Bug #30512043)
• Table and column identifiers in a FOREIGN KEY ... REFERENCES clause can be quoted within backticks (`). Alternatively, double quotation marks (") can be used if the ANSI_QUOTES SQL mode is enabled. The lower_case_table_names system variable setting is also taken into account.

Syntax: Naming convention for Foreign Key

Foreign key is a field in the database table that is a primary key in other tables. The naming conventions for a foreign key constraint should have an "FK_" prefix, followed by the target table name, followed by the source table name.

"FK_< TargetTable>_< SourceTable>"

Explanation:

• TargetTable is the name of the table that contains the Foreign Key, while SourceTable is the name of the table that contains the Primary Key.
• A "FK_" prefix should be added to each Foreign Key Name.
• Both table names should begin with a capital letter.
• To indicate plural, both table names should conclude with the character "s" (or "es").

Example 1:

FK_Employees_Projects
FK_Students_ContactNumbers
FK_Orders_Details

Example 2:

FK_Orders_Employees
FK_Items_Products