asked on

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

StephenCairns

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.

jsm11482

ASKER

Ok, can you explain the logic behind why I can not do this?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jsm11482

ASKER

This is SQL Server 2005, SET NULL is totally valid.

StephenCairns

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.

StephenCairns

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

jsm11482

ASKER

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?

SOLUTION

StephenCairns

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial