A foreign key is a column (or combination of columns) in a table that reference another table's primary key. It is used to establish and maintain relationships between tables. Foreign keys prevent actions that would leave the database in an inconsistent state. For example, they prevent the deletion of a record referenced by another table. They also enable the cascading of updates or deletes. When a record in the parent table is updated or deleted, the corresponding records in the child table are also updated or deleted.
The marketing team at a furniture company was having difficulty tracking customer purchases. Their data was spread across multiple databases, and it was becoming increasingly difficult to manage. The IT team suggested using a Foreign Key in the SQL database to link customer information from different databases and make it easier to track customer purchases. Lets help them with further information about foreign key.
Let us take an example to explain it:
For example, consider a database that stores information about students and the classes they take. The Classes table contains a primary key, ClassID, that is used to identify each class. The Students table contains a foreign key, ClassID, that references the ClassID from the Classes table. This foreign key relationship ensures that when a record is deleted from the Classes table, any related records in the Students table are also deleted. Likewise, when a record is updated in the Classes table, any related records in the Students table are updated.
SQL FOREIGN KEY constraint ON CREATE TABLE:
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... CONSTRAINT fk_name FOREIGN KEY (column1, column2, ... column_n) REFERENCES parent_table (parent_column1, parent_column2, ... parent_column_n) );
This syntax is used to create a new table with a FOREIGN KEY constraint. The CONSTRAINT clause specifies the name of the FOREIGN KEY constraint and the columns it applies to. The REFERENCES clause specifies the parent table and the columns in the parent table that the FOREIGN KEY constraint will reference. The columns in the parent table must have the same data type as the columns in the child table.
CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers (customer_id) );
This example creates a new table called orders with a column for the order ID, a column for the customer ID, and a column for the order date. The CONSTRAINT clause specifies that the customer_id column should have a FOREIGN KEY constraint, referencing the customer_id column in the parent table called customers. This means that the value in the customer_id column in the orders table must exist in the customer_id column in the customers table.
SQL FOREIGN KEY constraint for ALTER TABLE:
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES other_table(column_name);
Table Name: Orders
|Column Name||Data Type|
Table Name: Customers
|Column Name||Data Type|
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers(id);
This statement will add a foreign key constraint to the orders table, which will ensure that the customer_id column of the orders table only contains valid customer IDs that exist in the customers table. The foreign key will reference the id column of the customers table.
DROP SYNTAX for FOREIGN KEY COSTRAINT:
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;
Table Name: Customers
|Column Name||Data Type||Constraint|
|Country||INT||FOREIGN KEY REFERENCES Countries(CountryID)|
ALTER TABLE Customers DROP CONSTRAINT FK_Customers_Countries;
This statement will remove the foreign key constraint named FK_Customers_Countries from the Customers table. The foreign key constraint was likely used to ensure that any entries in the Customers table had a corresponding entry in the Countries table. After executing the statement, the foreign key constraint will no longer exist, and the relationship between the two tables will no longer be enforced.
Difference between primary key and foreign key in SQL:
It is a column or set of columns used to create a link between two or more tables.
It can accept multiple null values.
The Foreign Key column can contain duplicate values.
It cannot be used to define relationships between tables.
It can be changed.
The Foreign Key allowed the marketing team to easily track and analyze customer purchases, giving them valuable insight into customer spending trends and helping them to better target their marketing campaigns. Foreign Key was helpful for them and allowed the marketing team to better understand their customers and create more effective marketing strategies.
1. What is the purpose of a foreign key in SQL?
Answer: c. To link two tables
2. What type of relationship is defined by a foreign key?
Answer: a. One-to-many
3. What is a cascading delete foreign key constraint in SQL?
Answer: b. A foreign key that allows data to be deleted
4. What happens when a foreign key is not indexed in SQL?
Answer: a. The query will take longer to execute
Related Tutorials to watch
Top Articles toRead