Re: Surrogate Key vs Production Key

"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:73e20c6c.0410140055.2690e121_at_posting.google.com...
| "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
news:<RYGdnZkHtp-rsfDcRVn-og_at_comcast.com>...
|
| > if the early model is 'test-driven', it becomes very apparent that if an
| > employee has been assigned to department 10, and a vehicle 37 is
assigned to
| > department 14, the employee cannot ever be assigned to vehicle 37, since
the
| > assigned department does not match the vehicle department -- and that
just
| > requires FK constraints (conversely, if i try to reassign the employee
to
| > another department, i am prevented until the current vehicle assignment
is
| > removed)
|
| Not really. The foreign key contraints cannot enforce that
| UNLESS you make the assumption that the dept_id part of
| dept_id||pers_id above (multi-part fk to employee) will be
| the same as the dept_id part of dept_veh_id||veh_id
| (multi-part fk to department_vehicle). And there is
| no referential integrity syntax anywhere that will allow
| you to explain that two part FK columns must be equal.
| The syntax for referential integrity only has to do with PKs
| and FKs. Check constraints are NOT part of referential integrity.
| If they were, then of course you could use a constraint to say
| that dept_id = dept_veh_id. But then you can do EXACTLY
| the same with surrogate keys:
| dept_sur_id = dept_veh_sur_id.
|

dept_sur_id would identify a row in the department table, and would have an arbitrary value (say, 337)

dept_veh_sur_id would identify a row in the department_vehicle table, and would also have an arbitrary value (say 992)

a constraint that enforces the equality of two arbitrary values that are not in the same domain is meaningless

to enforce that the dept_veh_sur_id references a department_vehicle that is assigned the department identified by dept_sur_id 337, you do not need the arbitrary values to match (ie, dept_veh_sur_id = 337), you need to determine that the dept_veh_sur_id value identifies the row in department_vehicle which has the value 337 in its dept_sur_id column (department_vehicle.dept_sur_id) -- that is possible, but it requires code, not a CHECK constraint

++ mcs Received on Thu Oct 14 2004 - 06:30:46 CDT