Error Message when creating DB
-
noel - since you know all about linked servers - could you please help Bryan out if you have the time ?!
**ASCII stupid question, get a stupid ANSI !!!** -
SUSHILA!!!
What are you doing in SSC at these hours?
* Noel -
looking for words of wisdom from the gurus...sometimes i'm online round the clock -
**ASCII stupid question, get a stupid ANSI !!!** -
I would think that at these times you will probably be the only guru around
* Noel -
we have a saying in india which roughly translates to - "in the land of the blind, the one-eyed man is king"....
**ASCII stupid question, get a stupid ANSI !!!** -
I really admire your consistency and modesty!
I have seen very good suggestions coming from you and I am not afraid to tell you
* Noel -
Thanks guys! I'm trying to understand this issue. It didn't take me long to understand what was meant by circular references. However, I didn't see what was wrong with it. I thought "That's what i want!" So how do i fix it and maintain the integrity of my data?
Here is some information i found.
Multiple Cascading Actions
The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree containing no circular references. No table can appear more than once in the list of all cascading referential actions that result from the DELETE or UPDATE. The tree of cascading referential actions must not have more than one path to any given table. Any branch of the tree is terminated when it encounters a table for which NO ACTION has been specified or is the default.
WORKAROUND
To work around this problem, do not create a foreign key that will create more than one path to a table in a list of cascading referential actions.You can enforce referential integrity in several ways. Declarative Referential Integrity (DRI) is the most basic way, but it is also the least flexible way. If you need more flexibility, but you still want a high degree of integrity, you can use triggers instead.
------------------------------------------------------------------My problem is that in Access, i always checked for referential integrity. When there was a delete, all the child or secondary rows were deleted also.Looking at the the DB schema at http://www.geocities.com/jacobpressures, if an employee is deleted from the DB, everything on the bottom row should be deleted! EmployeeTests, Completed_Categories, Completed_Questions, and Responses. Of course, it should NOT deleted anything on the upper levels.Likewise, if Tests is deleted, all rows related to that test should also be deleted from the Categories table, Questions, Answers, etc. Since EmployeeTests, Completed_Categories, and all the others on the bottom row are dependent on the top row, they should be deleted also if the TestID or CategoryID is deleted.If a Category is deleted, you cannot have a reference to THAT category in the COMPLETED_Category table or a reference in the Questions table when there is nolonger any corresponding CategoryID in the Categories table!Looking at the diagram shows that the major relationships in my DB are CIRCULAR! Following the counsel to NOT CREATE "a foreign key that will create more than one path to a table in a list of cascading referential actions" would destroy the integrity of the data!I'm assuming that this rule is created in our best interest. But coming from an Access background, THIS IS FOOLISHNESS!And i don't know what I'm supposed to get from this statement: "Declarative Referential Integrity (DRI) is the most basic way, but it is also the least flexible way. If you need more flexibility, but you still want a high degree of integrity, you can use triggers instead. "WHY DID THEY EVEN CREATE THIS IF THEY WANT US TO GO BACK TO USING TRIGGERS!!!!!!I'm not angry. But this is frustrating! Can somebody explain? -
I don't know. For some reason, i had the impression that you had left but that you would answer when got back in. Then to see this comment, i thought that's funny! Thanks a great deal!
-
Not that you guys like my book MS SQL Server2000 Bible, but it makes it sound as if DRI using Cascade Deletes are preferable.
p 320 says: "Implementing cascade deletes manually is a lot of work. Because foreign-key constraints are checked before triggers, cascade-delete triggers don't work with SQL Server DRI via foreign keys. Therefore, not only will triggers have to handle the cascading delete, but they will have to perform RI checks as well."
p 133 says: "SQL Server's DRI can be enforce referential integrity without writing custom triggers or code." It next explains that DRI executes faster than custom RI triggers.
When mentioning CAUTIONS about cascading deletes, it doesn't mention anything about circular references.
-
Well it IS!! -- from performance stand point
The problem comes when you give someone the power to delete all child records from all cascaded tables which some times can wipe out the whole thing with a simple Delete * from OneSingleTable
If you gurrantie that only access to that table is through SPs and that only one SP can do that and that only VERY limitted number of users (preferably only ONE) has such power then I see no reason not to use it. I have in the past but took all those meassures before
Cheers!
* Noel -
To me that is just a matter of permissions not ability. Though i don't want to delete all the tests now, i would prefer to make them inactive, but years down the line, i would want the ability to delete TestID=1 if it is no longer in use.
The only way to do this seems to be through triggers. You guys have mentioned SPs. But do you mean queries that contain SQL statements? Well, i guess a trigger is a SP, but what exactly do you mean? Trigger or SP? Or it doesn't matter?
In this DB, I'm considering removing cascade deletes all together. At least via DRI. Is that a good idea? This reminds me of a statement Remi made.
I'm already using an alternative via an inactive column in quite a few of my tables.
Thanks!
-
With stored procedures you code manually the deletes starting from the lowest in the hierarchy! with triggers you could do the same thing but Triggers are more difficult to debug maintain and tend to generate "unexpected" issues that are associated with rollbacks on them.
It is cleaner to use stored procedures but is not as self maintained as it seems to be with triggers.
Just my $0.02
* Noel
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply