November 13, 2009 at 11:16 am
I have inherited a database that has two tables that have foreign key constraints on each other's primary keys. This seems very abnormal to me. Any idea why someone would ever do this? Or recommendations on what to do about it? I noticed it because the "View Dependency" on the tables was giving wierd results. It can't be good to be your own grandchild, right? Here's some details:
On RegistrationHistory table I have this constraint:
ALTER TABLE [DBO].[RegistrationHistory] WITH NOCHECK ADD CONSTRAINT [FK_RegistrationHistory_Registrations] FOREIGN KEY([RegistrationID])
REFERENCES [DBO].[Registrations] ([ID])
GO
On Registrations table I have this one:
ALTER TABLE [DBO].[Registrations] WITH NOCHECK ADD CONSTRAINT [FK_Registrations_RegistrationHistory] FOREIGN KEY([CurrentRegistrationHistoryID])
REFERENCES [DBO].[RegistrationHistory] ([ID])
GO
Thanks in advance for any advice or insight you can provide.
Tom
November 13, 2009 at 11:26 am
i dont really understand what you mean
November 13, 2009 at 11:34 am
Well, on either one of the tables, if you right-click and look at View Dependencies, you will see the other table in both "objects that depend on.." and in "Objects on which ... depends" sections.
I hope my explanation makes sense.
November 16, 2009 at 6:22 am
I don't think I'd do it personally, but I don't see a problem with it. Basically the original designer wanted to be able to reference the most recent or last valid row in History without having to look at Max date. The best way to ensure you have a valid historyid is to have a FK on the column. I assume that the CurrentRegistrationHistoryID column allows nulls. As long as you are not deleting History rows it shouldn't cause a problem.
The issue I would have is that now your update transactions have to update registrations, create a row in history and then update registrations again with the newest history id. I assume all this is done with triggers, which could cause other problems especially with debugging.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 16, 2009 at 1:06 pm
Thank you! Your explanation helped me understand what they did. They didn't use triggers though. I think they're doing it in the front end application. There are about two dozen stored procs that use the Registrations table, so I'll have to go through those and see if some of those are involved.
Again, thanks for your help. I've got a lot to learn about all of this.
November 16, 2009 at 1:47 pm
I'd think it would be tough to work with these tables starting from empty, I don't think you could satisfy the FK.. Those FK's must have been added AFTER there was some data.
I don't think I would ever do this, it is confusing and makes the two tables difficult to work with.
CEWII
November 17, 2009 at 10:14 am
It is a valid data model. I have designed and implemented it where the business case required it. I've also used it to prevent certain updates and/or deletions. However, it does create some problems for SQL Server vs. Oracle.
SQL Server, unlike Oracle, does not support deferred referential constraint checking. I.e., defer checking the FKs until the COMMIT. So in SQL Server one value has to be NULLable. An insert is performed which is then followed by an UPDATE to set the other FK value.
One other problem that I found with SQL Server vs. Oracle is that SQL Server (2000 and 2005) will not allow two FK constraints with the CASCADE Delete rule to be specified to the same parent table. The error received is:
[font="Arial Narrow"]Introducing FOREIGN KEY constraint 'name' on table 'table name' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.[/font]
However, Oracle (9.x and 10.x) does allow it.
So in this case I had to use triggers to perform some checking and to perform the cascade delete action.
November 17, 2009 at 1:47 pm
Is there a 1 in 10,000 chance I'd use it at some point sure.. But not without a substantial amount of fore-thought and then I'd still be inclined not to.. Valid or not I don't view it as a good practice.. Mileage may vary..
CEWII
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply