sql server 2019 foreign key on multiple columns from different tables-CodePudding

so i have to create three tables :

the first one is :

create table TblProf(profId int IDENTITY(1,1) PRIMARY KEY
                    ,profName nvarchar(50)
                    ,profUsername nvarchar(50));

and the second one is

create table TblStudent(studentId int IDENTITY(1,1) PRIMARY KEY
                         ,studentName nvarchar(50)
                         ,studentUsername nvarchar(50));

and i want to create the third table which should have two foreign keys the first one is profID from the table TblProf and the secaond one is studentId from the table TblStudent. so it's two foreign keys from two diffrent tables ,is it possible ? if so how should i create it?

CodePudding user response：

This is called a bridge table

CREATE TABLE bridge(
    refprofId int FOREIGN KEY REFERENCES TblProf(profId ),
    refstudentId  int FOREIGN KEY REFERENCES TblStudent(PersonID)
    ,PRIMARY KEY(refprofIdm,refstudentId));

you can add more columns, when the bridge has its own propertoes