June 24, 2009 at 10:10 am
i hv created the below table ,
create table fan_team(
fan_id smallint constraint fan_fk2 references fan(fan_id),
team_name varchar(15) constraint team_fk2 references team(team_name),
constraint f_t_pk primary key(fan_id, team_name)
)
but now i want to add a "on delete cascade" property on fan_id which has a reference to fan table,
when i enter the following code it displays some errors.
alter table fan_team
alter column fan_id smallint constraint fan_fk2 references fan(fan_id) on delete cascade
any help would be valuable.
thanks
June 24, 2009 at 11:18 am
Letme guess the error: SQL Server has detected cyclic foreign key cascades?
The only work around is to use a trigger OR USE a database vendor that is much more closer to the "relational model" like Oracle. Even an Access database can do cyclic foreign key cascades, and the cyclic cascade feature was requested way back in SQL 2000! They still haven't added it! This defeats the whole purpose of using referential integrity to do most of the work.
But Microsoft is happy if people keep writing kludge applications using surrogates identifiers, procedural coding, and proprietary stuff a relational database was never meant for. There's no "thinking" required.
The foundation of an RDMS is relational algebra but it is nice that Microsoft is promoting "beyond the relational model". It's not the relational model that has the problem, it's the implementation by Microsoft.
Check this out. The purpose of a user defined type is so you can defined a custom datatype (of a base) with specific constraints (like length). The problem is, once created.....you cannot alter without dropping and recreating. AND you cannot ALTER without dropping ALL REFERENCES to the type first. So you manually have to goto each table and stored procedure and remove the type.
June 24, 2009 at 9:34 pm
it means i hv to delete this table and all the depending tables and and restart creating , in order to add on delete cascade option.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply