• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

Multiple cascade paths...

Consider the following tables:

CREATE TABLE [dbo].[UserAccount]
(
      [UserAccountID] [uniqueidentifier] NOT NULL,
      CONSTRAINT [PK_UserAccount] PRIMARY KEY CLUSTERED ([UserAccountID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
      CONSTRAINT [IX_UserAccount] UNIQUE NONCLUSTERED ([UserName] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[SystemSetting]
(
      [SystemSettingID] [uniqueidentifier] NOT NULL,
      [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [ArrayIndex] [int] NULL,
      [Value] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [CreationUserID] [uniqueidentifier] NULL,
      [CreationDateTime] [smalldatetime] NOT NULL,
      [UpdateUserID] [uniqueidentifier] NULL,
      [UpdateDateTime] [smalldatetime] NOT NULL,
      CONSTRAINT [PK_SystemSetting] PRIMARY KEY CLUSTERED ([SystemSettingID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
       CONSTRAINT [IX_SystemSetting] UNIQUE NONCLUSTERED ([Name] ASC, [ArrayIndex] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
       CONSTRAINT [FK_SystemSetting_UserAccount] FOREIGN KEY (CreationUserID) REFERENCES dbo.UserAccount(UserAccountID) ON UPDATE CASCADE ON DELETE  SET NULL,
       CONSTRAINT [FK_SystemSetting_UserAccount1] FOREIGN KEY (UpdateUserID) REFERENCES dbo.UserAccount(UserAccountID) ON UPDATE CASCADE ON DELETE  SET NULL
) ON [PRIMARY]


UserAccount.UserID is referenced via foreign key from SystemSetting.CreationUserID and SystemSetting.UpdateUserID.  Both constraits have rules: Updated = Cascade, Delete = Set Null.

Thank you,
Josh
0
jsm11482
Asked:
jsm11482
  • 4
  • 3
2 Solutions
 
StephenCairnsCommented:
Not sure you can do that
I think that I would not attempt to enforce the referential integrity here
do the delete in a trigger if you have to.
0
 
jsm11482Author Commented:
Ok, can you explain the logic behind why I can not do this?
0
 
Scott PletcherSenior DBACommented:
You cannot specify "hints" in the constraint definition, so "WITH (IGNORE_DUP_KEY = OFF)" is not allowed.

Also, "SET NULL" is not a valid SQL Server option.  You can specify "NO ACTION" if you don't want it to cascade.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jsm11482Author Commented:
This is SQL Server 2005, SET NULL is totally valid.
0
 
StephenCairnsCommented:
You are enforcing referential integrity with your foreign key contraint.
your FK can be null or point to a record that must exist.
In the table this FK points to
   if your change the PK then the value in the FK table will change (never quite saw the point of this if the PK changes its not a PK in my opinion)
  if you delete the record then it either stops you because that would orphan the record or you delete all records that address it.

What you are asking to do does not follow this logic and can only be done in a trigger.



0
 
StephenCairnsCommented:
your CreationUserID and UpdateUserID fields form  type of audit trail. what is the logic of having it if when you delete the user all the history disappears?
In this situation, I would create an expiry date or expired flag on the User account if a record has expired hide it from general use, only use it when you want to see who updated the record.

What Scott was refering to was SET NULL is not a valid option within the contraint. Only CASCADE or NO ACTION are valid
0
 
jsm11482Author Commented:
Good point. In fact this is what I would usually do when a user is deleted, however I thought this may be bad/risky just because this is for a system which incorporates eCommerce.  Users would probably not like their information sitting on the server. is it ok to do?
0
 
StephenCairnsCommented:
It all depends on your data retention policy.
if you have to retain your data then use an expiry method (I like a date as that allows you to create an as at view)
if you must not retain data and the record belongs to them then cascade on delete
if the record does not belong to them then I'd point it to an "information removed" record
but if they have entered or edited information in a record is it not then theirs?

This is one for your company lawyers to figure out.

but again if you want the stump Information removed record then you would have to use a trigger
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now