Is partial dependency possible In Data Base for Foreign key relationship

Question

I have 3 tables 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 tables and data should be inserted in User table.

Answer 1

By defining a foreign key (FK) you are saying that the value I place in this table (the "child" or "referencing" table) must have a matching value already in the other table (the "parent" or "referenced" table). By defining User.user_id as a foreign key referencing both Student.id and also Teacher.id you are saying that the value in User must be in both Student and also in Teacher. That's just what a foreign key means.

What you want is to say that User.user_id must be in either Student OR Teacher. There is no way to define that. You can only reference a single object when defining a foreign key.

You may be able to create a single object by UNIONing Student and Teacher into a view. You don't say which DBMS you're using so I don't know if yours supports views as FK targets. (Actually, I don't know if any supports views as targets.) Since the referenced column must be unique you will have to ensure that Teacher and Student are disjoint on the referenced value.

It would be better to change the columns in User. Instead of user_id have teacher_id and student_id defined as NULLable. Each will have a FK referencing their specific table. A check constraint can ensure the one and only one of these is populated.

However, I think the real solution is to invert your hierarchy. A user is a more abstract concept than teacher or student. It should be the parent table. The others have a foreign key referencing User. Column User.User_type says which of the child tables is populated. This is called table inheritance. It is supported in the dialect of some DBMS. For others you must write application code.

Answer 2

Something I would be worried about with this approach is what will keep me from having a Student_id of 27 for Kevin, and a Teacher_ID of 27 for Mr. Jones? If you absolutely want to avoid this you could use a single base table for Person, then everyone get's their own number.

In keeping the current plan, I would pull the user_id field from the user table, and add a user_id field to the student and teacher tables. The student table would now have a foreign key to the Primary key of the user table, essentially going the opposite way you have it going here. Now a person's User_ID is part of their record.

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

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

CREATE TABLE User(User_ID INT NOT NULL PRIMARY KEY, User_Type(Char(1) NOT NULL, User_Name NVARCHAR(25) NOT NULL, User_Password NVARCHER(255) NOT NULL)


ALTER TABLE Student
ADD CONSTRAINT FK_Student FOREIGN KEY (User_ID) REFERENCES User (User_ID)
ALTER TABLE Teacher
ADD CONSTRAINT FK_Teacher FOREIGN KEY (User_ID) REFERENCES User (User_ID)