MSSQL DAL multiple cascade paths not allowed

[Omi Chiba]

I was fololwing the web2py book with mssql for my database.

db.define_table('comment',
Field('page_id', db.page),
Field('body', 'text'),
Field('created_on', 'datetime', default=request.now),
Field('created_by', db.auth_user, default=auth.user_id))

will generate

CREATE TABLE comment(
id INT IDENTITY PRIMARY KEY,
page_id INT NULL, CONSTRAINT comment_page_id__constraint FOREIGN
KEY (page_id) REFERENCES page(id) ON DELETE CASCADE,
body TEXT NULL,
created_on DATETIME NULL,
created_by INT NULL, CONSTRAINT comment_created_by__constraint
FOREIGN KEY (created_by) REFERENCES auth_user(id) ON DELETE CASCADE
)

and will cause the error says:

Introducing FOREIGN KEY constraint 'comment_created_by__constraint' on
table 'comment' 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.

The details for the error is descrived in here
http://support.microsoft.com/kb/321843

After I made the change on dal.py it worked fine.

def __init__(
self,
fieldname,
type='string',
length=None,
default=DEFAULT,
required=False,
requires=DEFAULT,
# ondelete='CASCADE',
ondelete='NO ACTION',

Can we make this change as path to avoid the error for mssql ? I'm not
sure if other database has the same problem.

[Massimo Di Pierro]

I would prefer to leave this as it is for backward compatibility
reasons but you can do Field(...., ondelete="no action") correct?
In any case open an issue on google code.

On Aug 17, 2:16 pm, Omi Chiba <ochib...@gmail.com> wrote:
> I was fololwing the web2py book with mssql for my database.
>
> db.define_table('comment',
>     Field('page_id', db.page),
>     Field('body', 'text'),
>     Field('created_on', 'datetime', default=request.now),
>     Field('created_by', db.auth_user, default=auth.user_id))
>
> will generate
>
> CREATE TABLE comment(
>     id INT IDENTITY PRIMARY KEY,
>     page_id INT NULL, CONSTRAINT comment_page_id__constraint FOREIGN
> KEY (page_id) REFERENCES page(id) ON DELETE CASCADE,
>     body TEXT NULL,
>     created_on DATETIME NULL,
>     created_by INT NULL, CONSTRAINT comment_created_by__constraint
> FOREIGN KEY (created_by) REFERENCES auth_user(id) ON DELETE CASCADE
> )
>
> and will cause the error says:
>
> Introducing FOREIGN KEY constraint 'comment_created_by__constraint' on
> table 'comment' 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.
>

> The details for the error is descrived in herehttp://support.microsoft.com/kb/321843