How to relate 2 tables

Ramco Ramco

3y989SQL

I have 2 tables

1. Student Master

Student ID Primary , Name , Address

2. Student Detail

ID Primary (Identity), Student ID, Join Date , Marks

How i can relate 2 tables so that Student ID in table 1 must exists.

Thanks

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago

Accepted answer

You can use a foreign key in the "Student Detail" table that references the "Student ID" primary key in the "Student Master" table. This way, you can ensure that a student ID in the "Student Detail" table must exist in the "Student Master" table.

-- Student Master table
CREATE TABLE StudentMaster (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Address VARCHAR(100)
);

-- Student Detail table
CREATE TABLE StudentDetail (
    ID INT PRIMARY KEY IDENTITY(1,1),
    StudentID INT,
    JoinDate DATE,
    Marks INT,
    FOREIGN KEY (StudentID) REFERENCES StudentMaster(StudentID)
);

Vishal Yelve3y ago
Hi Ramco,

As per Amit suggestion you need to add foreign key in StudentDetail table. which refernce to StudentId of Student Master table.

Also refer below links

https://csharp-video-tutorials.blogspot.com/2012/08/creating-and-working-with-tables-part-3.html

https://www.youtube.com/watch?v=TpKcAmaaBts

https://www.youtube.com/watch?v=JLeaM8pK8dE

https://www.guru99.com/sql-server-foreign-key.html
0

Amit Mohanty3y ago

If your tables are already exists the you can alter the table to add foregin key.

      ALTER TABLE [StudentDetail]
   ADD CONSTRAINT [FK_StudentDetail_StudentMaster]	
      FOREIGN KEY ([StudentID])						
       REFERENCES [StudentMaster] ([StudentID])		
ON DELETE CASCADE;	-- Optional, specify the desired behavior for deleting referenced rows

Ramco Ramco3y ago
Hi Amit

Can u pls guide how to do thru GUI

Thanks
0
Pasang Tamang3y ago
Hi Rmaco,

You have to use foreign key in your second table. In Student Detail table column Student ID should be foreign key. You can get detail idea about foreign key here https://www.w3schools.com/sql/sql_foreignkey.asp.

Regards,
Pasang
+1