Many of u faced this problem (SQL) Many To Many Relationship

Navjot Angra

8y912

I know after Reading This U will also this about it ,but if u want to know please upvote and help to me to solve

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 in Table B,I know Most of u will Say no,

but suppose I have Rquirement To do it how it is possible without removing Unique Constraint.

Please If Anybody have any idea ,Please share as soon as poosible

Please upvote this question so that I get good answers from experts ,

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jayesh Vaghasiya8y ago
try this way

CREATE TABLE department
(
ID INT IDENTITY PRIMARY KEY,
DeptName VARCHAR(256),
);
INSERT INTO dbo.department VALUES ('CSE');
INSERT INTO dbo.department VALUES ('Mech');
INSERT INTO dbo.department VALUES ('Civil');
CREATE TABLE student
(
StudentId INT IDENTITY PRIMARY KEY,
Name VARCHAR(256),
DepartmentId int unique foreign key references Student(StudentId),
);
INSERT INTO student VALUES ('nav',1);
INSERT INTO student VALUES ('Angra',2);
INSERT INTO student VALUES ('navjot',3);
Navjot Angra8y ago
@Jignesh,
Can u please elaborate it with example?
Jignesh Kumar8y ago
As per good database design,
I would suggest as department table is master table so there is no dependency on table student. So please remove that and the student belongs to department so put departmentId as FK in student table.

Apart from this one more approch,
you can create one more table which will contain mapping for StudentId and DepatmentId but this approch is not recommended.

Thanks
Navjot Angra8y ago
@Jignesh,
Actullay few days ago I was giving an interview ,Then Interviewer asked me this question?
I was not able to answer this question ,that is why posted it
Jignesh Kumar8y ago
Hi,

I have question on your table design ? is there any significance of your required studentId in Department table ?

If yes, please explain scenario when you need this id in deparment table ?