Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to enforce DB integrity with non-unique foreign keys?

I want to have a database table that keeps data with revision history (like pages on Wikipedia). I thought that a good idea would be to have two columns that identify the row: (name, version). So a sample table would look like this:

TABLE PERSONS:
    id:      int,
    name:    varchar(30),
    version: int,
    ... // some data assigned to that person.

So if users want to update person's data, they don't make an UPDATE -- instead, they create a new PERSONS row with the same name but different version value. Data shown to the user (for given name) is the one with highest version.

I have a second table, say, DOGS, that references persons in PERSONS table:

TABLE DOGS:
    id:         int,
    name:       varchar(30),
    owner_name: varchar(30),
    ...

Obviously, owner_name is a reference to PERSONS.name, but I cannot declare it as a Foreign Key (in MS SQL Server), because PERSONS.name is not unique!

Question: How, then, in MS SQL Server 2008, should I ensure database integrity (i.e., that for each DOG, there exists at least one row in PERSONS such that its PERSON.name == DOG.owner_name)?

I'm looking for the most elegant solution -- I know I could use triggers on PERSONS table, but this is not as declarative and elegant as I want it to be. Any ideas?


Additional Information

The design above has the following advantage that if I need to, I can "remember" a person's current id (or (name, version) pair) and I'm sure that data in that row will never be changed. This is important e.g. if I put this person's data as part of a document that is then printed and in 5 years someone might want to print a copy of it exactly unchanged (e.g. with the same data as today), then this will be very easy for them to do.

Maybe you can think of a completely different design that achieves the same purpose and its integrity can be enforced easier (preferably with foreign keys or other constraints)?


Edit: Thanks to Michael Gattuso's answer, I discovered another way this relationship can be described. There are two solutions, which I posted as answers. Please vote which one you like better.

like image 822
DzinX Avatar asked Oct 09 '09 12:10

DzinX


People also ask

Can a foreign key reference non unique?

A FOREIGN KEY constraint that references a non- UNIQUE key is not standard SQL but rather an InnoDB extension. The NDB storage engine, on the other hand, requires an explicit unique key (or primary key) on any column referenced as a foreign key.

How foreign keys help maintain database integrity?

A foreign key relationship allows you to declare that an index in one table is related to an index in another and allows you to place constraints on what may be done to the table containing the foreign key. The database enforces the rules of this relationship to maintain referential integrity.

How do you enforce integrity constraints in a database?

To enforce data integrity, you can constrain or restrict the data values that users can insert, delete, or update in the database. For example, the integrity of data in the pubs2 and pubs3 databases requires that a book title in the titles table must have a publisher in the publishers table.

Does foreign key constraint enforce uniqueness?

No, foreign keys are not implicitly unique.


1 Answers

In your parent table, create a unique constraint on (id, version). Add version column to your child table, and use a check constraint to make sure that it is always 0. Use a FK constraint to map (parentid, version) to your parent table.

like image 100
A-K Avatar answered Sep 28 '22 05:09

A-K