I need help.

[wiesiek]

Can anybody explains me why following sql statements do not work with mssql?

create table place (id int primary key, name varchar(64), parent_id int
references place (id) on delete cascade);

create table transport (id int primary key, name varchar(64));
create table trip (id int primary key, from_id int references transport
(id) on delete cascade, to_id int references transport(id) on delete
cascade);

Is there any work around?
Wiesiek

[darkagn]

What errors do you get when you try to run the queries?

[wiesiek]

What errors do you get when you try to run the queries?

Here are mssql answers:

create table place (id int primary key, name varchar(64), parent_id int
references place (id) on delete cascade);

Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'FK__place__parent_id__014935CB' on table 'place'
may cause 
cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO
ACTION, or modify 
other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

create table transport (id int primary key, name varchar(64));
create table trip (id int primary key, from_id int references transport
(id) on delete cascade, to_id int references transport(id) on delete
cascade);

Msg 1785, Level 16, State 0, Line 2
Introducing FOREIGN KEY constraint 'FK__trip__to_id__0AD2A005' on table 'trip' may
cause cycles or 
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or
modify other FOREIGN 
KEY constraints.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

[dickersonka]

The issue is because you are placing the constraints on the same table

Think of it this way, if you remove a trip in the middle of the sequence, it will cause all other trips that are fk'd from it to be deleted as well

do as the error message tells you and on your fk's instead of on delete cacsade, set to on delete no action

[wiesiek]

The issue is because you are placing the constraints on the same table

Think of it this way, if you remove a trip in the middle of the sequence, it will cause all other trips that are fk'd from it to be deleted as well

do as the error message tells you and on your fk's instead of on delete cacsade, set to on delete no action

Thank You for your advice. I will try. But the problem is that with PostgreSQL everything work well:

create table place (id int primary key, name varchar(64), parent_id  
references place (id) on delete cascade);
OSTRZEŻENIE:  CREATE TABLE / PRIMARY KEY will create implicit index "place_pkey" for table "place"
CREATE TABLE

create table transport (id int primary key, name varchar(64));
OSTRZEŻENIE:  CREATE TABLE / PRIMARY KEY will create implicit index "transport_pkey" for table "transport"
CREATE TABLE

create table trip (id int primary key, from_id int references transport
(id) on delete cascade, to_id int references transport(id) on delete
cascade);
OSTRZEŻENIE:  CREATE TABLE / PRIMARY KEY will create implicit index "trip_pkey" for table "trip"
CREATE TABLE

[darkagn]

Thank You for your advice. I will try. But the problem is that with PostgreSQL everything work well

Unfortunately different implementations of SQL can often have different ways of doing things. PostgreSQL is actually closer to PL/SQL and even MySQL than MSSQL in implementation. This means that portability of your queries is an issue. dickersonka has given you some sound advice for your MSSQL version.

[wiesiek]

Unfortunately different implementations of SQL can often have different ways of doing things. PostgreSQL is actually closer to PL/SQL and even MySQL than MSSQL in implementation. This means that portability of your queries is an issue. dickersonka has given you some sound advice for your MSSQL version.

Thank You. Yesterday I found in MSSQL 2005 documentation:

"The series of cascading referential actions triggered by a single DELETE or UPDATE
must form a tree that contains no circular references. 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. Any branch of the tree is ended when it
encounters a table for which NO ACTION has been specified or is the default."