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.