SQL Server – Representing Many-to-Many Relationship Between Students and Departments

database-designsql server

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      |
---------------------------------

Best Answer

A many-to-many relationship requires three tables - the Entities themselves (Student and Department) plus the "weak entity" that connects them. 

select * from student ; 

+-----------+--------+ 
| StudentId | Name   |
+-----------+--------+ 
|         1 | nav    | 
|         2 | Angra  | 
|         3 | navjot | 
+-----------+--------+ 

select * from department ; 

+----+----------+ 
| ID | DeptName | 
+----+----------+ 
| 11 | CSE      | 
| 22 | Mech     | 
| 33 | Civil    | 
+----+----------+ 

create table dept_student 
( StudentId    int not null foreign key references Student( StudentId )
, DepartmentId int not null foreign key references Department( Id )
, primary key ( StudentId, DepartmentId )
);

select * from dept_student ; 

+-----------+--------------+
| StudentID | DepartmentID | 
+-----------+--------------+
|         1 |           11 |
|         2 |           22 |
|         1 |           33 |
+-----------+--------------+
Related Question