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.
