Irshad Alam said:
I have found Two primary key (One Numeric & other one Text) in the
same Table. Designed by another programmer.
Could you please explain with an example tips, how it can be benifit
and in what situation we can use this.
That's not really two primary keys, since a table can have only one
primary key; rather, it's a primary key that is composed of two fields.
This is called a "compound" or "composite" primary key. You can have
such a key any time it takes more than one field to uniquely identify a
record in the table.
The most common use for such a key is in the middle table of a
one-to-many-to-one relationship, which is a way to represent a
many-to-many relationship between to tables. For example, suppose you
have a school database with a Students table (primary key StudentID,
number) and a Courses table (primary key CourseName, text). Now suppose
you want a table to store which students are taking which courses. This
is effectively a many-to-many relationship between Students and Courses:
a student takes many courses, a course is taught to many students. So
you create a table like this:
StudentsCourses
-----------------
StudentID (number, PK, fk to Students)
CourseName (text, PK, fk to Courses)
The relationships of the tables are like this:
Students 1 <----> N StudentsCourses N <----> Courses
The compound primary key of StudentsCourses, composed of StudentID and
CourseID, ensures that only one record can be added to StudentsCourses
to link a particular student to a particular course.
