Tuesday, August 19, 2008

Composite Key

  • This is a very basic SQL topic but I think it is necessary to clarify the concept.
  • A Primary Key uniquely identifies each row in a table, it is not always a single-column key,it could be
    • a single-column key
    • or a composite key
  • A primary key can consist of one or more columns of a table. When two or more columns are used as a primary key, they are called a composite key. Each single column's data can be duplicated but the combination values of these columns cannot be duplicated.
  • For example, if you have a Student table and a Course table, and one student can select many courses and one course can be selected by many students, so this is a many-to-many relationship. So you need to create the third table to define the relationship, say it's called StudentCourse. It is important to note that you only need the StudentID and CourseID in this table as a composite key. You do not need an extra identity ID column in this table to uniquely identifies each row because only having an ID column to uniquely identifies each row is not sufficient. It cannot prevent the same student selecting the same course from being inserted into this table.


Composite Primary Keys

Primary Key

blog comments powered by Disqus