1

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      |
---------------------------------
5
  • 2
    What exactly do you mean with "insert upper values"?
    – user1822
    Aug 23, 2018 at 5:56
  • 1
    Please edit the question and add more details. What is the expected result set and what's the actual one? If you have any DML statements you've tried, add those as well. Any error messages from executing said statements would be of use too.
    – vonPryz
    Aug 23, 2018 at 6:00
  • #a_horse_with_no_name,I want multiple entries in 2nd table having studentid =1
    – Nav
    Aug 23, 2018 at 6:10
  • 1
    Have you concidered adding a many-to-many table for the relationship between student and department? Aug 23, 2018 at 7:27
  • #a_horse_with_no_name ,Like I want to insert values (upper values means )that i am inserting into table B
    – Nav
    Aug 23, 2018 at 11:50

4 Answers 4

0

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 |
+-----------+--------------+
3
  • Thanks ,But Can I Use Unique constraint in my table as I defined
    – Nav
    Aug 23, 2018 at 11:31
  • I was trying to avoid spoon feeding an answer but never mind Aug 23, 2018 at 13:16
  • @Nav: You question would suggest that your method is not working. If that's the case, I would say "no"; you can't do it that way.
    – Phill W.
    Aug 23, 2018 at 15:23
1

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);
0

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

-1
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);
1
  • 2
    You may want to merge your accounts, you will then be able to edit your answer instead of posting a duplicate.
    – mustaccio
    Aug 23, 2018 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.