Relational Integrity and Candidate Keys
On my current project, we're not using natural, semantic keys: we're using Guids for primary keys and enforcing unique constraints on the combination of columns that make up the candidate keys. And this is fine for most purposes, but there's an aspect of relational integrity that gets left out.
Consider a system that schedules events within multiple buildings for multiple organizations. Some tables might include the following:
Assume the obvious foreign key relationships and implied Organization parent table.
Now, you will want your SpeakerId in the Event table to be of a speaker whose Speaker record has the same Organization Id as the Organizational Schedule record does. But how are you going to relate the records in order to assure this?
In SQL Server 2008, I don't think you can, not directly, unless you're using natural keys as the actual primary keys. So I'm looking at check constraints that use UDFs and the possibility of triggers.
Consider a system that schedules events within multiple buildings for multiple organizations. Some tables might include the following:
OrganizationalSchedule
ScheduleId PRIMARY KEY
OrganizationId
FiscalYearId
Speakers
SpeakerId PRIMARY KEY
OrganizationId
Name
Event
EventId PRIMARY KEY
OrganizationalScheduleId
SpeakerId
Date
Assume the obvious foreign key relationships and implied Organization parent table.
Now, you will want your SpeakerId in the Event table to be of a speaker whose Speaker record has the same Organization Id as the Organizational Schedule record does. But how are you going to relate the records in order to assure this?
In SQL Server 2008, I don't think you can, not directly, unless you're using natural keys as the actual primary keys. So I'm looking at check constraints that use UDFs and the possibility of triggers.
0 Comments:
Post a Comment
<< Home