SQL Foreign Key Constraint - Foreign Key Constraint in SQL
FOREIGN KEY Constraint
- FOREIGN KEY is like a connection lock that ensures tables play nice. It's a field in one table that matches the main ID in another.
- Think of it as a child (table) always pointing to its parent (table), keeping things organized.
Wikitechy Demo Table
Student Details
- Note that the 'StudentID' column in the 'Student Detail' table is linked to the 'StudentID' column in the 'Wikitechy' table. The 'StudentID' column in the 'Wikitechy' table functions as the PRIMARY KEY in the 'Wikitechy' table.
- Conversely, the 'StudentID' column in the 'Student Detail' table is like a code that connects to the 'Wikitechy' table. The FOREIGN KEY makes sure we only put valid student IDs, keeping our data in check.
FOREIGN KEY on CREATE TABLE
-
The next SQL step is like giving a green light for the 'StudentID' column in the 'Student Detail' table to connect with the ‘Wikitechy’ table.
Syntax
CREATE TABLE Student_Detail ( Student_Id INT, place VARCHAR(50), CONSTRAINT fk_Student_Id FOREIGN KEY(Student_Id) REFERENCES tbl_Wikitechy (Student_Id) )
Output
Step 1:
Step 2:
FOREIGN KEY on ALTER TABLE
- To establish a FOREIGN KEY constraint on the 'Student ID' column after the 'Wikitechy' table is already created, use the following SQL:
Syntax
ALTER TABLE Student_Detail ADD CONSTRAINT FK_Student_Id FOREIGN KEY (Student_Id) REFERENCES tbl_Wikitechy(Student_Id);
Output
Step 1:
Step 2:
DROP a FOREIGN KEY Constraint
- To remove a FOREIGN KEY constraint, use the following SQL:
Syntax
ALTER TABLE Student_Detail DROP CONSTRAINT FK_student_Id;
Output
Step 1:
Step 2:
