I’m new with SQL Server and while setting up the Yii-Auth extension (which uses CDbAuthManager) I got an error while creating the AuthItemChild table:
create table [AuthItemChild]
[parent] varchar(64) not null,
[child] varchar(64) not null,
primary key ([parent],[child]),
foreign key ([parent]) references [AuthItem] ([name]) on delete cascade on update cascade,
foreign key ([child]) references [AuthItem] ([name]) on delete cascade on update cascade
The error says something about " FOREIGN KEY constraint ‘FK_AuthItem_ …’ on table ‘AuthItem’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints." This happens when creating the second foreign key (the one for the ‘child’ column).
I’ve read that this is a SQL Server “feature” and can be worked around using triggers to emulate the cascade actions of the foreign key but I don’t understand how to do it. Any ideas?
Hi, and thank you for your help. Unfortunately the article you referenced doesn’t address the problem I’m facing, which is specifically related to the AuthItemChild table definition under MS SQL Server. I’ve used MySQL for the same thing with no problems, but MSSQL is “special”. If you come up with any more ideas I’ll appreciate it.