CODEWITHSUNDEEP
mysqlsqlconstraintscomposite-key
ram
ram

Reputation: 159

Does Composite keys allows Duplicate

I need to know whether Composite keys allows a duplicate values in it?

For eg:

I have 2 tables.

1) Table 'Student' which has fields studentID(PK),standard,address.

2) Table 'Class ' which has fields classID(PK),classname,studentID(FK).

By having the composite key as (classID,studentID), Does it allows Duplicate Values??

Exactly what I want to know is, is there any possibility to enter duplicate values in the classID field of Class Table? Because I cant enter duplicate values into classID as it is a primary key field.

Will Composite key help me in achieving this? Kindly help me in this.

Upvotes: 1

Views: 8970

Answers (3)

C.Champagne
C.Champagne

Reputation: 5489

In a composite key, the whole set of elements must be unique but each element can be repeated several times.

Thus,

  • if you have several records in Student having studentID = 1, 2, 3...
  • and the same for Class

-> then your third table having the composite key

(1, 1)
(2, 2)
(1, 2)
...

You can see that several records have the same studentId (1 here) or classId (2 here) but the the whole combination is never twice the same.

Upvotes: 5

ram
ram

Reputation: 159

It is allowing duplicates. The only possibility which is not allowed is the duplicates of the composite keys(studentID,classID) as like "C.Champagne" said. So we can have combinations like (1,2)(1,3)(2,3) but not (1,2) or (1,3) again.

And the second answer to my question was that I failed to add composite key for 3rd table('school') by using the primary keys of first 2 tables.

Eg: 3rd table 'school' has fields schoolID(PK),classID(FK),studentID(FK). But the problem I faced was that I cant add a composite key for the table 'school' with fields(classID,studentID,schoolID). The Mistake Which I made was ,I tried to add Foreign key to the School table by giving reference to 1st two tables and it dint work. What I have given at first was like:

ALTER TABLE school ADD FOREIGN KEY (studentID) REFERENCES student(studentID);

ALTER TABLE school ADD FOREIGN KEY (classID) REFERENCES class(classID);

The mistake I made was I tried to add foreign keys from both class and student tables.

But Finally the below worked by adding foreign key to school table was :

ALTER TABLE school ADD FOREIGN KEY (studentID,classID) REFERENCES class(studentID,classID);

Here giving reference as class table is more than enough as the class table is already holding studentID,classID.

So thats how it worked finally.

Upvotes: 3

Chris Cooper
Chris Cooper

Reputation: 157

With the structure you currently list, you're not going to have any classes with more than one student in. I think you've misunderstood the purpose of a composite key. In order to normalise this database, you'd expect a third table of EnrolledStudents or similar, which contained a composite primary key:


        Student
        studentID(PK)

        Class
        classID(PK)

        EnrolledStudent
        studentID(PK) (FK on Student.studentID)
        classID(PK) (FK on Class.classID)

That way you ensure you don't end up with invalid duplicates anywhere.

Upvotes: 0

Related Questions