Friday, April 02, 2010

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:

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