RI, cascade and trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

RI, cascade and trigger

Author

Topic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-18 : 09:26:21

Hi,

This is the scenario, I have 4 tables (cat,cub,dim,cubdim)
cat -< cub
cat -< dim
cub -< cubdim >- dim

What I want is to cascade all deletes and updates from parent tables to the child tables.
However I get the folowing error:
"Server: Msg 1785, Level 16, State 1, Line 22
Introducing FOREIGN KEY constraint 'FK__cubdim__5D01B3B4' on table 'cubdim' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

So my options are:
1. redesign the tables
2. create trigger to cascade RI.
3. ?

I opted for option 2,
but I guess I need an IOF TRIGGER ?

any suggestions anyone.

set nocount on

create table cat( cat char(10) not null primary key )

create table cub(
	cat char(10) not null references cat(cat) on update cascade on delete cascade
	,cub char(10) not null
	,primary key(cat,cub) )

create table dim(
	cat char(10) not null references cat(cat) on update cascade on delete cascade
	,dim char(10) not null
	,primary key(cat,dim) )	

create table cubdim(
	cat char(10) not null
	,cub char(10) not null
	,dim char(10) not null
	,foreign key(cat,cub) references cub(cat,cub) on update cascade on delete cascade
	,foreign key(cat,dim) references dim(cat,dim) -- on update cascade on delete cascade /*can't create*/
)

print 'insert some data...'
insert cat(cat) values('c1'); insert cub(cat,cub) values('c1','cub1');
insert dim(cat,dim) values('c1','dim1'); insert cubdim(cat,cub,dim) values('c1','cub1','dim1')
select * from cub; select * from dim; select * from cubdim

print 'update c1 to c0 ok, cascade updates cat throug all tables'
update cat set cat = 'c0' where cat = 'c1'
select * from cub; select * from dim; select * from cubdim

print 'update cub0 to cub1 ok, cascade updates cub throug all tables'
update cub set cub = 'cub0' where cub = 'cub1'
select * from cub; select * from dim; select * from cubdim

print /**/'update not ok, but I want to casacade the update to the cubdim table!'/**/
update dim set dim = 'dim0' where dim = 'dim1'
select * from cub; select * from dim; select * from cubdim
go

drop table cubdim
drop table dim
drop table cub
drop table cat

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-18 : 09:51:08

can you describe how these 4 tables relate in non-database terms; i.e., high level overview in a few sentences to describe them. I can't visualize in an abstract sense what this schema is trying to model.

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-18 : 10:16:40

will this do ?
olapcatalog -< dimensions
olapcatalog -< cubes
cubes -< cube_dimension >- dimension (edit)

the pk of an olapcatalog is just the catalog name ( could put server in )
the pk of a dimension is the catalog name + dimension name
the pk of a cube is the catalog name + cube name
And we have a M:M relationship between cubes and dimensions. ( which dims in which cubes )

What I want is cascading RI if something changes in one of the parent tables.
My conclusion so far is that I have to implement a IOF Trigger on the dimensions table.
However, in the case of a multirow UPDATE, then I am not sure how to relate the inserted and deleted tables.
Is it possible to relate them by the physical "rownumber" ?

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-20 : 14:56:42

......... bump .............
The question didn't make any sense ?

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-20 : 15:30:08

doesn't sql server support auto cascade that would solve your problem?

EDIT: after rereading it once more i gues that auto cascade is causing you troubles, no?

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-20 : 15:45:10

Yes spirit,
the setup is basically this:


  A
 / \.
B   C
 \ /
  D

with natural keys set up the way I did
I can't get cascade from B to D and C to D

A pk a
B pk a+b
C pk a+c
D pk a+c+b (edit)

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-20 : 15:57:52

how bout if you put an update trigger on A that updates B and C and put one on C that updates B.
that way "a" should be the same evereywhere, "b" will mind it's own busines in B and trigger on C will update
D.

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-20 : 16:49:57

trigger on A does:
- update B first
- update C second

trigger on C updates D with data in inserted abd last inserted row in B
i guess to do this you'd need a column to get the last inserted row in B
would that work?

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-20 : 17:17:55

I don't think the following construct is possible in SQL Server.
Is the design fundamentally wrong, or is there something I am missing ?


create table A(
	a int primary key)

create table B(
	a int references A(a) on update cascade on delete cascade,
	b int,
	primary key(a,b))

create table C(
	a int references A(a) on update cascade on delete cascade,
	c int,
	primary key(a,c))

create table D(
	a int,
	b int,
	c int,
	primary key(a,b,c),
	foreign key(a,b) references B(a,b) on update cascade on delete cascade,
	foreign key(a,c) references C(a,c) on update cascade on delete cascade)

drop table D
drop table C
drop table B
drop table A

kind regards,

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-20 : 17:20:43

you'll have to cascade them manually, as you said it doesn't work on auto...

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-20 : 17:42:02

Thanks for the response Spirit.

Now, if I cascade them manually, then what happens in a multirow UPDATE.
For each key I need to know what the old + new value is, so I can cascade correctly.
In the following example how can I be sure that 1 is updated to 2, and 2 to 3 ?,
the only way I can relate the rows is by their physical ordering.

Is that 100% reliable ???


create table A(a int primary key)

GO
create trigger trgA on A for update as
select * from deleted
select * from inserted
GO

insert A select 1 union select 2
update A set a = a + 1

drop table a

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-20 : 17:51:12

Of course there is no physical ordering.. ( slap forehead )
and no way to relate the rows in a multirow UPDATE.

create table A(a int primary key)

GO
create trigger trgA on A for update as
select * from deleted
select * from inserted
GO

insert A select 1 union select -2
update A set a = abs(a)

drop table a

So now I claim that the construct in my previous post
is not possible in sql server with natural keys and cascading RI.

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-20 : 17:53:44

well if you update 5 rows in A
then in the trigger you'll have 5 PK which you can use to update correct values in B and C.
in the Trigger on C you'll also have correct 5 values because of the update with trigger on A.
so you can also update correct values in D. or am i completly wrong??

maybe you should consider redesigning the whole thing.
this smells very much like circular reference hell to happen...

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-20 : 17:56:19

i believe it's not possible too... but i'll be glad if someone proves us wrong...

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-20 : 18:04:35

Ok Spirit, now You see the same thing I do I guess.

Either it's a flaw in the design of sql server, or in the physical design I created, or we have missed something.
Either way I would be happy for more enlightment.

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-20 : 18:13:24

i saw it before when i reread your post. that's whay i suggested you do it with your own triggers like i said. it would be interesting to see if it works...

i don't think it's a flaw in sql server. more likely it's a flaw in your design

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-20 : 18:30:03

quote:
Originally posted by spirit1
i don't think it's a flaw in sql server. more likely it's a flaw in your design

HeHehehehe..
I can do the physical design in other ways, but I don't think there is anything wrong with it.
(except it can't cascade changes, which is a limitation /*not flaw*/ in sql server).

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-20 : 18:33:34

so how will you go about it in the end?

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-20 : 19:35:08

Well,
I can use a system generated PK (e.g. identity) and let the natural keys be unique indexes instead.
That way the natural keys are demoted to candidate iof primary keys.

This schema stores metadata,
let's say we have databases and tables:
What are good PK's and CK's ?
option 1: database(database_name,pk(database_name)), table(database_name,table_name,pk(database_name,table_name))
option 2: database(dbid,database_name,pk(dbid),unique(database_name)), table(tabid,dbid,table_name,pk(tabid),unique(dbid,table_name))

Option 1 got me into some problems, because the names are not stable and can be updated. I was hoping I could solve this by cascading RI.
Option 2 will solve that problem.

I'll just stick identities in and thus insulate the schema from changes in the "natural keys".
a pk should be stable.

Oh well, the flaw in my physical design is "unstable pk's".
e.g. sp_renamedb makes database_name unsuitable as pk....

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-20 : 20:53:02

great!

just one question:
why did you put so many columns in the PK in the first place??
wouldn't be enough for table "table" to have a table_name as PK and db as not null ordinary column??

ok that were two questions

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-21 : 06:55:28

insert table(database_name,table_name) values('db1','table1')
insert table(database_name,table_name) values('db2','table1')
You have to have more than table_name as pk, say server+database+owner+table would be enough.

rockmoose

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources