Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2002
    Posts
    2

    Exclamation Unanswered: cycles or multiple cascade paths FOREIGN KEY constraints

    I have two tables partner and purchase_order

    Purchase order have two fields customer and agent which are referring to foreign table partner. Both customer and agent are kept in same table since they have lot of common attributes

    create table partner
    (partner_id integer primary key,
    partner_name varchar(50),
    partner_type char(1))

    create table purchase_order
    (Po_no integer primary key,
    Customer integer,
    Agent integer,
    Foreign key (Customer) references partner on delete cascade,
    Foreign key (Agent) references partner on delete cascade )

    Ignore SQL syntax ( above syntax works for oracle)

    While creating purchase_order table , it gives errors

    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_123' on table 'purchase_order ' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint

    How can I achieve this ?

    One way is to write a trigger (Instead of delete) on purchase_order , But that according to me is not an elegent solution
    Other way to create separate tables for customer and agent which also should be avoided. Because , then I have to create multiple sets of same table with same table structure


    FOR INFORMATION , ORACLE ALLOWS CREATION OF THE TABLES SMOOTHLY and WORKS PERFECTLY

    Is there a way to get past the problem, without redesigning the database in SQL Server 2000 ?
    Last edited by asitsaha; 02-21-03 at 11:43.

  2. #2
    Join Date
    May 2002
    Posts
    299

    Re: cycles or multiple cascade paths FOREIGN KEY constraints

    Unfortunately you have to either splitting up the customer/agent or using insteadof trigger in sqlserver.

    g'luck.
    --
    -oj
    http://www.rac4sql.net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •