Can I have an "identity" (unique, non-repeating) column span multiple tables? For example, let's say I have two tables: Books and Authors.
Authors
AuthorID
AuthorName
Books
BookID
BookTitle
The BookID column and the AuthorID column are identity columns. I want the identity part to span both columns. So, if there is an AuthorID with a value of 123, then there cannot be a BookID with a value of 123. And vice versa.
I hope that makes sense.
Is this possible?
Thanks.
Why do I want to do this? I am writing an ASP.NET MVC app. I am creating a comment section. Authors can have comments. Books can have comments. I want to be able to pass an entity ID (a book ID or an author ID) to an action and have the action pull up all the corresponding comments. The action won't care if it's a book or an author or whatever. Sound reasonable?
The short answer is: No, you can't do that (at least in MS SQL Server through 2008).
You could make a new table, "CommentableEntity", plug your identity column in there, then define foreign keys in Authors and Books to reference it as a parent table, and then do one of a number of tricks to ensure that a given ID value is not assigned to both tables... but this is a poor idea, because the data model you built would imply that Authors and Books are related kinds of data, and they really aren't.
You could have a separate table, Comments, have the identity column in there, and park a CommentId column in both Authors and Books. However, that would limit each book and author to only one comment.
Me, I'd probably add a column like "CommentorType" to the Comments table and a put a flag in there indicating source of comment ("A" for author, "B" for book). Build a primary key on "CommentorId + CommentorType", and it should work well enough -- and it'd be trivial to add further types of commentors as the system expands.