"James A. Fortune" wrote in message
...

Personally, I don't take the natural keys out either, so they can still
be used for the deletion.

There are really two issues being discussed in a single discussion here.

The issue of synthetic keys versus natural keys is one issue. The issue of
a composite PK in a junction table, made up of FKs, versus a new simple key
is a separable issue.

Please note that, if the two FKs under discussion both reference synthetic
PKs, all of your arguments concerning the problems of dealing with natural
keys become moot.

If we have three tables, Students, Courses, and Enrollments, where
enrollments is a junction between Students and Courses, we could have a
synthetic key, StudentID for students, and a synthetic key, CourseID, for
Courses.

The question then remains which is simpler. To define enrollments with a
composite key
(StudentID, CourseID), or to define a new synthetic key, EnrollmentID.
Neither of these two solutions uses natural keys.

I prefer to se natural keys whenever possible, but I use synthetic keys
when natural ones just won't do. When do natural keys fail to do the job?
When the poeple who control them are mismanaging them.