autoexcrement (4/18/2013)
2) If I end up with a bit field in the names table to indicate a "real" name, what is the best way to enforce a rule that each person has not less than nor greater than 1 "real" name flagged in the "names" table?
Just becuase I happened to ask (and then answer) the same question to myself, have you seen Filtered Indexes in SQL2008+?
Given:
CREATE TABLE people (
person_id INTEGER IDENTITY(1, 1) PRIMARY KEY
);
CREATE TABLE names (
name_id INTEGER IDENTITY(1, 1) PRIMARY KEY,
person_id INTEGER FOREIGN KEY REFERENCES people(person_id),
name NVARCHAR(MAX) NOT NULL,
is_primary BIT NOT NULL
);
...the index:
CREATE UNIQUE NONCLUSTERED INDEX uqfi_names
ON names (person_id)
WHERE is_primary = 1
...should technically only allow one row for each person_id to be the 'primary'.
J.