Suppose I have two tables
Table A=Student
Table B=Department
Table A:
CREATE TABLE student
(
StudentId INT IDENTITY PRIMARY KEY,
Name VARCHAR(255),
DepartmentId VARCHAR(255),
);
INSERT INTO student VALUES ('nav',1);
INSERT INTO student VALUES ('Angra',2);
INSERT INTO student VALUES ('navjot',3);
Table B
CREATE TABLE department
(
ID INT PRIMARY KEY,
DeptName VARCHAR(255),
studentId int unique foreign key references Student(StudentId)
);
INSERT INTO dbo.department VALUES (101, 'CSE', 1);
INSERT INTO dbo.department VALUES (102, 'Mech', 2);
INSERT INTO dbo.department VALUES (103, 'Civil', 1);
Now the question is:
Can I insert upper values(Values that i given above insert into table B) in Table B, I know most of you will say no.
But suppose I have requirement to do it how it is possible without removing unique constraint.
Note: Actually In first table I have DepartmentId=1, I want that more than one row should insert in table b having studentid =1
(against one Departmentid [In Table A], I can add multiple StudentId [table B] in table B)
Output will be like this:
----------------------------------
ID DeptName studentId |
101 CSE 1 |
102 Mech 2 |
103 Civil 1 |
---------------------------------
many-to-many
table for the relationship between student and department?