How to relate 2 tables

Question

Hi

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

Amit Mohanty · 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) );

Amit Mohanty · Answer

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

Pasang Tamang · Answer

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

Vishal Yelve · Answer

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

Ramco Ramco · Answer

Hi Amit Can u pls guide how to do thru GUI Thanks

How to relate 2 tables

Answers (5)