Representing a Many-to-Many Relationship Between Students and Departments

Question

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

Answer 1

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

Answer 2

In Sql not allowed duplicate data in Unique key. but you can create combination of two column of unique key like CONSTRAINT UC_Person UNIQUE (ID, StudentId) This will solve your problem.

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);
select * from student
select * from department
drop table student
drop table department

CREATE TABLE department
(
ID INT PRIMARY KEY,
DeptName VARCHAR(255),
studentId int foreign key references Student(StudentId)
**CONSTRAINT UC_Person UNIQUE (ID,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);

Answer 3

The data model you have is insufficient for the data and (more importantly) the relationships you are trying to capture. I would recommend reading up about normalisation and understand what’s happening with normal forms and why you would go through this process https://en.m.wikipedia.org/wiki/Database_normalization. Eventually with experience you won’t go through the rigmarole of laying everything out and going through the forms in order - you will eventually just think and build immediately in the form that you require/ is appropriate for the problem at hand.

You should also read through this https://en.m.wikipedia.org/wiki/Many-to-many_(data_model) and the links available there and try to gain an understanding of what’s happening.

From all of the above you will find the answer you seek and will hopefully be able to apply that knowledge to future problems :-)

Answer 4

In Sql not allowed duplicate data in Unique key.
but you can create combination of two column of unique key like 
**CONSTRAINT UC_Person UNIQUE (ID, StudentId)**
This will solve your problem.


    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);
    select * from student
    select * from department
    drop table student
    drop table department

    CREATE TABLE department
    (
    ID INT PRIMARY KEY,
    DeptName VARCHAR(255),
    studentId int foreign key references Student(StudentId)
    **CONSTRAINT UC_Person UNIQUE (ID,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);