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"? – a_horse_with_no_name Aug 23 '18 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 '18 at 6:00
  • #a_horse_with_no_name,I want multiple entries in 2nd table having studentid =1 – Nav Aug 23 '18 at 6:10
  • 1
    Have you concidered adding a many-to-many table for the relationship between student and department? – Lennart Aug 23 '18 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 '18 at 11:50
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 '18 at 11:31
  • I was trying to avoid spoon feeding an answer but never mind – CasualFisher Aug 23 '18 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 '18 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 '18 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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