February 20, 2006 at 12:13 pm
Guys,
I'm kind of new to SQL Server.
I created a db with quite a few tables. Some of these tables are used as lookups for an app.
I then created a diagram model to show the various relationships between the tables. However I needed to add and drop some tables but kept getting an error saying table x cannot be dropped because it is referenced by a Foreign Key constraint.
So rather stupidly I thought by deleting the relationship diagram I would remove the relationships !
Unfortunately I have now deleted the diagram model and still cannot drop any tables as the above message persists. I have two questions:
1. How do I resolve the situation I have created ?
2. Is it possible to create a relationship diagram without actually implementing the relationships ?
Any help would be much appreciated !
Jas
February 21, 2006 at 3:31 am
If a table has a foreign key referencing it, it can't be dropped.
(Effectively the other tables are populated with data in the table you are trying to drop, hence it's a non-starter)
You need to drop the foreign key constraints you put on the other tables.
February 21, 2006 at 7:22 am
As for "2. Is it possible to create a relationship diagram without actually implementing the relationships ?"
You can create a diagram with no referential integrity by clearing all three checkboxes at the bottom of the relationship Properties window (the two 'Cascade...' checks will disable when you clear the 3rd checkbox). You'll still have the diagram but no dependencies between the tables. You can then drop tables with these "relationships" without the error you were getting. The next time you open the diagram, EM will tell you the tables no longer exist and remove them from the diagram.
Note that once your DB is "finalized" you should implement referential integrity to define the true data relationships and to have SQL do the enforcement of the rules between your data elements.
*** Oh shoot... I just noticed this was originally posted under "SQL 2005". My answer pertains to SQL 2000. It might be the same in 2005, or it might be different. Dunno...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply