need advise on DELETE action.

Hi friends
i've a table that has 3 columns that refer to same parent table(foreign keys).am trying to following as part of action.

ALTER TABLE dbo.u_childTable ADD CONSTRAINT
FK_childTable_MASTER_BATCHTO FOREIGN KEY
(
FK_BATCHTO_MASTERID
) REFERENCES dbo.u_master
(
MASTERID
) ON UPDATE NO ACTION
ON DELETE NO ACTION

GO
ALTER TABLE dbo.u_childTable ADD CONSTRAINT
FK_childTable_MASTER_FEESCALER FOREIGN KEY
(
FK_FEESCALER_MASTERID
) REFERENCES dbo.u_master
(
MASTERID
) ON UPDATE set null
ON DELETE set null

GO
ALTER TABLE dbo.u_childTable ADD CONSTRAINT
FK_childTable_MASTER_SUBDEBT FOREIGN KEY
(
FK_SUBDEBT_MASTERID
) REFERENCES dbo.u_master
(
MASTERID
) ON UPDATE set null
ON DELETE set null

GO

but it wont let me complaining with following error
"Introducing

FOREIGN KEY constraint 'bl..blahh' on table 'childTable' may cause cycles or

multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO

ACTION, or modify other FOREIGN KEY constraints."

what am i missing here
You may only have one constraint and one refernece action for an action defined for the relation between two tables in your situation.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Please check out the Books Online topic below for restrictions on using cascading actions on referential constraints. They are same for SQL Server 2000 and 2005.

http://msdn2.microsoft.com/en-us/library/ms186973.aspx

|||

Thanks for the post Jens.

i dont think i got the point but while investigation this issue i found following MSDN link

http://support.microsoft.com/kb/q321843/

above link says that

"the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree. "

but am not specifying any "cascade" as action. am setting either "SET NULL" or "NO ACTION".

much appreciated if you can guide me to understand this concept better.

|||or does it mean that i also need to check other foreign keys defined on this child table ?|||Any SET option (SET NULL or SET DEFAULT) is also a cascading action. So the restriction applies to SET NULL, SET DEFAULT and CASCADE. SET NULL|DEFAULT is a special case of cascade where the value is set to NULL or DEFAULT on column(s) respectively.|||

Thanks for the post Umachandar.

it also says same as my above link

" No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table."

is there any way to get the this tree graphically .

btw how would you handle such a scenario with database desgin ?

Thanks

|||You could get the tree using a recursive CTE in SQL Server 2005. The way to handle this if you hit the restrictions is to enforce the referential integrity rules (part of it) using triggers in SQL Server.|||>>The

way to handle this if you hit the restrictions is to enforce the

referential integrity rules (part of it) using triggers in SQL Server.

Thanks for this. Thats what we decided to do now.

>>You could get the tree using a recursive CTE in SQL Server 2005.
i know CTE but dont know how to use in this regard. can you give me some idea
Thanks again
|||

See below link for more details on how to write a recursive CTE:

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

|||Thanks for that.
Thats what i've been looking and am getting error

"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

btw am trying this
with joins as
(
select child,child_column from vJoins
where parent='mytable'
union all
select fkeys.child,fkeys.child_column from vJoins fkeys
inner join joins on fkeys.parent = joins.child
)
select * from joins

btw the view "vJoins" is nothing but list from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view

can you see what the error is?
Thanks for ur help|||

The error message happens because you are not specifying the termination condition for the recursion. See below for an example:

with consts (child_schema, child_table, parent_schema, parent_table)
as (
select ctu1.TABLE_SCHEMA, ctu1.TABLE_NAME, ctu2.TABLE_SCHEMA, ctu2.TABLE_NAME
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc
join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE as ctu1
on ctu1.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA

and ctu1.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE as ctu2
on ctu2.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA

and ctu2.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
),
const_tree (child_schema, child_table, parent_schema, parent_table, path, level)
as (

/* get all parents except for the self-referential ones, we will do that later */
select distinct parent_schema, parent_table
, cast(NULL as nvarchar(128)), cast(NULL as nvarchar(128))

, cast(N'' as nvarchar(4000)), cast(0 as int)
from consts
where not (child_schema = parent_schema and child_table = parent_table)
union all
select fk.child_schema, fk.child_table, fk.parent_schema, fk.parent_table
, p.path + '/' + quotename(p.child_schema) + '.' + quotename(p.child_table)
, p.level + 1
from consts as fk
join const_tree as p
on fk.parent_schema = p.child_schema
and fk.parent_table = p.child_table
)
select parent_schema, parent_table, child_schema, child_table, path, level
from const_tree
where level > 0
union all
select parent_schema, parent_table, child_schema, child_table, '/' + quotename(child_schema) + '.' + quotename(child_table), 0
from consts
where (child_schema = parent_schema and child_table = parent_table)
order by path;

|||

wonderful UmaChandar

will give it a try tomorrow and update you.

Thanks again :)

|||hi UmaChandar
i tried that and still getting same error with one of our main (bigger) databases
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

how can i troubleshoot it?
btw it works with one of our smaller databases which has very few joins.
Thank you very much|||It is possible that there are many ways to get cycles with referential constraints. The simple case with the self-referential constraint was easy to eliminate. You can usually determine the rows that cause the cycles by looking at the partial result set. Look for rows that have repeating values for example. Once you locate such rows, you need to identity how the cycle happens so that you could detect it in the recursive member of CTE.