Cascade delete problem..

Hi,

I have a Configuration Table tblConfig with 45 fields-

PK_Config_Id

ConfigCreatedOn

ConfigEditedOn

ConfigStartDate

ConfigEndDate

ConfigFor

….

….

….

And an Employee table tblEmployee

PK_Emp_Id

EmpName

….

….

….

I would create a Configuration and then apply this config to some Employees —

So, I created a table tblEmployee_Config with 2 fields

FK_Config_Id

FK_Emp_Id

But I MAY need to change(Edit) few of the properties(fields)ofthe Configuration record associated to an emp. Therefore I need all the fields of tblConfig table along with employee key in tblEmployee_Config

So, I modified tblEmployee_Config to —

FK_Emp_Id

FK_Config_Id

ConfigCreatedOn

ConfigEditedOn

ConfigStartDate

ConfigEndDate

ConfigFor

….

….

….

i.e. putting all the fields of the tblConfig into tblEmployee_Config along with emp key

But instead of having 2 almost similar tables (tblConfig and tblEmployee_Config), I merged both the tables into 1 tablehaving Configurations as well as Employees associated to Configs as—

PK_Config_Id

FK_Emp_Id

FK_Config_Id

ConfigCreatedOn

ConfigEditedOn

ConfigStartDate

ConfigEndDate

ConfigFor

….

….

….Where FK_Emp_Id and FK_Config_Id would be NULL for Configurations.

Like-

PK_Config_IdFK_Emp_IdFK_Config_IdAllowBrksWorkHrs

1NULLNULLYes9

21001Yes9

3 1011No8

Here record 1 is a Configuration and records 2 and 3 are Emps records that are associated to Config 1.

Now my question is –

1. Is it correct if I put a relation like

Primary key Foreign Key

tblEmployee_ConfigtblEmployee_Config

PK_Config_IdFK_Config_Id

2. Can I perform cascade delete on the same table tblEmployee_Config such that–

When record 1 is deleted, records 2 and 3 should also get deleted because they refer to the first record?

You will either have to use triggers on the tables or use CTEs to DELETE the rows with one statement.

Jens K. Suessmeyer

http://www.sqlserver2005.de