SQL Foreign Key
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
Look at the following two tables:
Notice that the “PersonID” column in the “Orders” table points to the “PersonID” column in the “Persons” table.
– The “PersonID” column in the “Persons” table is the PRIMARY KEY in the “Persons” table.
– The “PersonID” column in the “Orders” table is a FOREIGN KEY in the “Orders” table.
– The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table
Example:
CREATE TABLE tablename(ColumnName1 Datatype(SIZE) PRIMARY KEY, ColumnNameN Datatype(SIZE) FOREIGN KEY REFERENCES PARENT_TABLE_NAME(Primary_Key_Of_Parent_tbl));
The following SQL creates a FOREIGN KEY on the “PersonID” column when the “Orders” table is created:
CREATE TABLE Orders (
OrderID int PRIMARY KEY,
OrderNumber int ,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Orders (
OrderID int ,
OrderNumber int ,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
To create a FOREIGN KEY constraint on the “PersonID” column when the “Orders” table is already created, use the following SQL:
ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
To allow the naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
To drop a FOREIGN KEY constraint, use the following SQL:
ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;
Course Video
1.Create two table in 1st table set primary key and insert 10 records and in 2nd table set FOREIGN key and insert 5 records in it.
2.Create two tables the second table should have a foreign key referencing the first table. Also, insert sample data into both tables
3.Create table without foreign key and add foreign key using alter table
Hint: A foreign key constraint is added to the Budget table referencing the customer id column in the Customer table.
4.Create table with constrain primary key foreign key and unique key
Hint: A unique constraint is placed on the order id. A foreign key constraint is added to ensure referential integrity between the Orders table and the Customers table.1
5.write sql query to drop foreign key constrain
6. Design and implement a SQL script to establish two relational tables named “Courses” and “Students.” The “Courses” table should include columns for CourseID (integer, primary key), CourseName (string) and credits(int). The “Students” table should comprise columns for StudentID (integer, primary key), StudentName (string), and CourseID (integer). Furthermore, implement a foreign key constraint in the “Students” table to ensure referential integrity by linking the CourseID column to the corresponding column in the “Courses” table.
7. Design and execute a SQL script to establish two relational tables named “Customers” and “Orders.” The “Customers” table should include columns for CustomerID (integer, primary key) and CustomerName (string). The “Orders” table should comprise columns for OrderID (integer, primary key), OrderDate (date), and CustomerID (integer). Furthermore, implement a foreign key constraint in the “Orders” table to ensure referential integrity by linking the CustomerID column to the corresponding column in the “Customers” table.
8. Write an SQL query to retrieve all CountryRegionCode entries where the Name starts with the letter ‘A’ or ‘G’ and the ModifiedDate is earlier than January 1, 2009. From Person_CountryRegion. [OUTPUT: 30 ROWS]
[HINT: use ADVENTURE2019 Database]
9. Craft a comprehensive SQL script to initialize two distinct tables, namely “Library” and “Books,” devoid of any foreign key constraints. The “Library” table should include columns for LibraryID (integer, primary key) and LibraryName (string). Correspondingly, the “Books” table should include columns for BookID (integer, primary key), Title (string), and LibraryID (integer). Subsequently, integrate a foreign key constraint into the “Books” table to establish a reference to the LibraryID column within the Library table. Finally, execute insertion operations to populate both tables with relevant data. And try to insert the record that violet the foreign key constraint.
10. write a SQL query to drop the foreign key constraint from the Books table.
YouTube Reference :
1) SQL Foreign Key in Hindi/Urdu
2) SQL Foreign Key in English
A SQL FOREIGN KEY is a constraint used to link two tables. It ensures that the value in a column (or a combination of columns) matches a value in a referenced column of another table.
You can define a foreign key either during table creation or by altering an existing table:
- During Table Creation:
sqlCopy codeCREATE TABLE Orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) );
- Altering an Existing Table:
sqlCopy codeALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES Customers(customer_id);
- Always ensure the referenced column is a primary key or unique key.
- Use meaningful names for foreign key constraints for better readability.
Avoid creating circular references between tables.
In a one-to-many relationship, the foreign key is added to the table on the “many” side to reference the “one” side. For example, in an order management system:
- Customers (one): Primary key customer_id.
- Orders (many): Foreign key customer_id referencing Customers.
Yes, a foreign key can have NULL values, which means the record does not yet relate to any record in the referenced table.
- Referential Integrity Violation: Attempting to delete or update a parent row that is referenced by a foreign key.
Mismatched Data Types: Ensuring that the data type of the foreign key column matches the referenced column.
- PRIMARY KEY: Ensures uniqueness within the same table and cannot contain NULL values.
- FOREIGN KEY: Ensures data consistency between two related tables.
Visit our SQL FOREIGN KEY Tutorial for detailed examples and best practices.
Yes, use the following SQL command to drop a foreign key constraint:
sqlCopy codeALTER TABLE Orders DROP CONSTRAINT fk_customer;
Cascading actions like ON DELETE CASCADE and ON UPDATE CASCADE automatically delete or update related rows in child tables when the parent table changes. Example:
sqlCopy codeCREATE TABLE Orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE );
