How to Create a Table With Multiple Foreign Keys in SQL?
Last Updated : 26 Sep, 2021
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.
Syntax:
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:
Query:
CREATE DATABASE GeeksforGeeks
Step 2: Using the Database
To use the GeeksforGeeks database use the below command:
Query:
USE GeeksforGeeks
Step 3: Creating 3 tables. The table student_details contains two foreign keys that reference the tables student_branch_details and student_address.
Query:
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) );
Output:
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:
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
Query:
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:
Query:
SELECT * FROM student_details SELECT * FROM student_branch_details SELECT * FROM student_address
Output:
Similar Reads
How to Create a Table With a Foreign Key in SQL?
A foreign key is a column or a set of columns in one table that references the primary key of another table. Foreign keys are used to establish and enforce a link between the data in two tables, ensuring referential integrity in the relational database system. In this article, we will explain how to
6 min read
SQL Query to Create Table With a Primary Key
A primary key is essential in SQL databases to uniquely identify each row in a table. It ensures data integrity by containing only unique and non-NULL values. A table can have only one primary key, which can consist of a single column or multiple columns, called a composite key. In this article, we
5 min read
How to add a foreign key using ALTER in MySQL
In this article, we will discuss the overview of foreign keys and will discuss how to add a foreign key using ALTER in MySQL step by step. Let's discuss it one by one. Foreign key : If an attribute is a primary key in one table but was not used as a primary key in another table then the attribute wh
3 min read
How to List all Foreign Keys Referencing a Given Table in SQL Server?
SQL Server is a Relational Database Management System(RDBMS) that allows users to create and manage databases efficiently. In SQL Server, understanding the table's relationship is very important for database design and maintenance. Foreign keys play an important role in establishing relations betwee
5 min read
Foreign Key with a Null Value in PostgreSQL
PostgreSQL supports foreign keys to ensure referential integrity between tables. Nullable foreign keys add flexibility by allowing child table rows to either reference a parent table or have a NULL value, meaning no association. While this is useful in certain cases, managing nullable foreign keys c
6 min read
How to Delete a Foreign Key Constraint in SQL
Foreign key constraints are important for maintaining referential integrity in a relational database as they define relationships between tables. However, there are times when we may need to identify which foreign key constraints reference a specific table in SQL Server. This could be necessary for
4 min read
How to Retrieve Data from Multiple Tables in SQL?
In SQL, retrieving data from multiple tables is a common requirement in database operations. Efficiently combining data from different tables allows developers to create complex queries and extract valuable insights from interconnected datasets. In this article, we will explore multiple approaches t
5 min read
How to Temporarily Disable a Foreign Key Constraint in MySQL?
MySQL is a popular open-source relational database management system (RDBMS) that is uniquely used to construct expandable and high-productivity databases. MySQL, which was created by MySQL AB and later acquired by its current owner Oracle Corporation, was originally introduced in 1995. MySQL is rep
4 min read
How to Left Join Multiple Tables in SQL
Left Join is one of the Keywords used while writing queries in SQL. In SQL we normally use Join for the purpose of forming a new table by taking out common data like rows or records or tuples from both the tables which are having matching records in general. Here when it comes to Left Join in SQL it
3 min read
Foreign Key with a Null Value in MySQL
In databases, foreign keys are like links connecting two tables. They make sure that data in one table matches data in another. But a common question is whether a foreign key can be NULL. In this article, We will explain what NULL values mean for foreign keys, how they work with the help of examples
5 min read