Is a single field contain multiple foreign key relationship from different table [SQL]

Question

I have 3 table Student,Teacher,User.

Student:

CREATE TABLE Student( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL);
INSERT INTO [dbo].[Student]([id],[name]) VALUES(4,'Ram'),(5,'Raman');

Teacher:

CREATE TABLE Teacher( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL);
INSERT INTO [dbo].[Student]([id],[name]) VALUES(1,'Raj'),(2,'Rahul');

User:

CREATE TABLE [dbo].[User](
    id INT NOT NULL PRIMARY KEY,
    user_id INT NOT NULL,
    user_type CHAR(1) NOT NULL,
    user_name VARCHAR(10) NOT NULL,
    user_password VARCHAR(255) NOT NULL,

    CONSTRAINT  FOREIGN KEY (user_id) REFERENCES Student (id),
    CONSTRAINT  FOREIGN KEY (user_id) REFERENCES Teacher (id) );

Now I try to INSERT in User table with below query

INSERT INTO [dbo].[User] ([id] ,[user_id]  ,[user_type],[user_name] ,[user_password])   VALUES  (1 ,1,'S','Raj_001','********')

It gives me error for violation of foreign key due to value of user_id is available in Teacher and not in Student

So my question is: How can I achieve that a value of user_id is present in one of those table and data should be inserted in User table.

Answer 1

Your table structure is flawed. A foreign key tells the database that there is definitely one and only one row on one side of the relationship. You can't have a partial match, it's all or nothing. This is before considering how you would ensure that you don't end up with the same id in both the teacher and student table.

It would be better to have two columns in your user table, one for teacher id and one for student id. In fact going further given the only extra data in both student and teacher tables is their name why not just eliminate both and store the name in the user table?

Another option to consider is that your foreign key is pointed in the wrong direction. Perhaps a better approach is reversing it to ensure each student and teacher is a user rather than that a user is either a student or a teacher.

Answer 2

First of all get rid of those key words from table name like [User],user_id etc.

It really is problematic and irritating.

Secondly why 2 key in [User] table,id, user_id ? It is not require.

I will keep only id or user_id.

Thirdly, knowing the real table structure or even purpose of each table help in better data modeling.

From [User] table what it appear is that id and user_type are composite primary key.

It should be. If this is true then you can't define FK constraint, as user_type is not available in either Teacher table and Student Table.

And what is appear that ,for example first data is inserted in Student or Teacher then data is inserted in User table in same Transaction.

So in all above scenario, Instead of Trigger is ideal scenario in this condition.

My script is just demo,

Create Proc spStudentInsert
as
set nocount on
set xact_abort  on
begin try
begin tran

--bulk insert or single insert ,no problem
insert into Student

insert into [User]

if (@@Trancount>0)
commit
end try
begin catch
if (@@Trancount>0)
rollback
end catch

CREATE TRIGGER INSTEADOF_TR_I_User ON [user]
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @Flag BIT = 1

    IF NOT EXISTS (
            SELECT 1
            FROM Student S
            INNER JOIN inserted i ON i.id = S.id
            )
        SET @Flag = 0
    ELSE IF NOT EXISTS (
            SELECT 1
            FROM Teacher T
            INNER JOIN inserted i ON i.id = T.id
            )
        AND @Flag = 1
        SET @Flag = 0

    IF (@Flag = 0)
    BEGIN
        RAISERROR (
                N'Invalid user'
                ,16
                ,1
                )

        RETURN
    END
END

In case I am wrong about id, user_type composite PK then you can do other way,

PK of User id is FK in Student table as well as Teacher table. Also , id are PK in their respective table.

So first you insert in User table then you insert in Student or Teacher table.

So design in this case will be,

CREATE TABLE [dbo].[User](
    id INT NOT NULL ,

    user_type CHAR(1) NOT NULL,
    user_name VARCHAR(10) NOT NULL,
    user_password VARCHAR(255) NOT NULL,
    CONSTRAINT [PK_user] PRIMARY KEY (id)
    )
    INSERT INTO [dbo].[User] ([id] ,[user_type],[user_name] ,[user_password])   
    VALUES  (1 ,1,'S','Ram_001','********')
    --drop table [User]
    --alter table [user]
    -- drop constraint PK_user
CREATE TABLE Student( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL);

ALTER TABLE Student
 add CONSTRAINT FK_StudentUser  FOREIGN KEY (id) REFERENCES [User] (id);

INSERT INTO [dbo].[Student]([id],[name]) VALUES(1,'Ram'),(5,'Raman');

--select * from [Student]

CREATE TABLE Teacher( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL);

ALTER TABLE Teacher
 add CONSTRAINT FK_TeacherUser  FOREIGN KEY (id) REFERENCES [User] (id);

INSERT INTO [dbo].Teacher([id],[name]) VALUES(1,'Raj'),(2,'Rahul');

So what it appear from your question, I will create Instead of Trigger and go with that model.