Foreign Key SQL : Everything You Need To Know About Foreign Key Operations

 

What is Foreign Key constraint?

A foreign key is a type of key used to link two tables in a database. So, a foreign key is an attribute or a collection of attributes in one table that refers to the primary key in another table.

For Example, if Table A and Table B are related to each other, then if Table A consists of the primary key, this table would be called the referenced table or parent table. Similarly, if Table B consists of a foreign key, then that table is known as the referencing table or child table.

Now that you know what is foreign key, next in this article on Foreign key SQL, let us understand the rules of the foreign key.Read More SQL Server Developer Online Training

Rules for Foreign key

The Rules of Foreign Key are as follows:

1. The table with the foreign key is called the child table and the table being referenced by the foreign key is called the parent table.
2. Null values are allowed in a foreign key
3. Foreign keys can be duplicated
4. There can be more than a single foreign key in a table
5. The relationship established between the tables is known as referential integrity 

Now that you know what are the rules of a foreign key, next in this article on Foreign key SQL, let us see the operations of the foreign key.Know More SQL Server Developer Online Course

Foreign Key Operations:

To understand the various operations present on Foreign key, consider the following two tables:

Customer Table:

CustomerID	CustomerName	PhoneNumber
1	Rohan	9876543210
2	Sonali	9876567864
3	Ajay	9966448811
4	Geeta	9765432786
5	Shubham	9944888756

Courses Table:

CourseID	CourseName	CustomerID
c01	DevOps	2
c02	Machine Learning	4
c03	RPA	1
c04	Tableau	3
c05	AWS	2

Now, if you observe, the customerID column in the courses table refers to the customerID column in the customers’ table. The customerID column from the customers’ table is the Primary Key and the customerID column from the courses table is the Foreign Key of that table.Read More SQL Server Developer Training

Foreign Key on Create Table

You can use the following syntax to create a foreign key on the “customerID” column when you create “courses” table:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

#For SQL Server/ MS Access/ Oracle CREATE TABLE courses ( courseID varchar NOT NULL PRIMARY KEY, courseName varchar NOT NULL, customerID int FOREIGN KEY REFERENCES customers(customerID) ); #For MySQL CREATE TABLE courses ( courseID varchar NOT NULL PRIMARY KEY, courseName varchar NOT NULL, customerID int PRIMARY KEY (courseID), FOREIGN KEY (customerID) REFERENCES customers(customerID) );

Apply Foreign Key on Multiple Columns

To apply foreign key on multiple columns while creating a table, refer to the following example:

1 2 3 4 5 6 7

CREATE TABLE courses ( courseID varchar NOT NULL, courseName varchar NOT NULL, customerID int, PRIMARY KEY (courseID), CONSTRAINT FK_CustomerCourse FOREIGN KEY (customerID) REFERENCES customers(customerID) );

Next, in this article on Foreign Key SQL, let us see how to use the foreign key on Alter Table.

Foreign Key on Alter Table

You can use the following syntax to create a foreign key on the “customerID” column when the “courses” table is already created and you just want to alter the table:

1 2	ALTER TABLE courses ADD FOREIGN KEY (customerID) REFERENCES customers(customerID);

If you wish to add a name to the Foreign Key constraint and define it on multiple columns, use the following SQL syntax:

1 2 3

ALTER TABLE courses ADD CONSTRAINT FK_CustomerCourse FOREIGN KEY (customerID) REFERENCES Customers(customerID);

Drop Foreign Key

To drop the foreign key, you can refer to the following example:

1 2 3 4 5 6

#For SQL Server/ MS Access/ Oracle ALTER TABLE courses DROP CONSTRAINT FK_CustomerCourse; For MYSQL ALTER TABLE courses DROP FOREIGN KEY FK_CustomerCourse;

Know More SQL Server Developer Online Training