How to Create a Table With Multiple Foreign Keys in SQL?
When a non-prime attribute column in one table references the primary key and has the same column as the column of the table which is prime attribute is called a foreign key. It lays the relation between the two tables which majorly helps in the normalization of the tables. A table can have multiple foreign keys based on the requirement.
In this article let us see how to create a table with multiple foreign keys in MSSQL.
column_name(non_prime) data_type REFERENCES table_name(column_name(prime)
Step 1: Creating a Database
We use the below command to create a database named GeeksforGeeks:
CREATE DATABASE GeeksforGeeks
Step 2: Using the Database
To use the GeeksforGeeks database use the below command:
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) );
The number and type of keys can be checked in the tables section of object explorer on the left side of the UI.
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
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.