Post reply

Foreign key problem

  • January 7, 2011 at 5:22 am

    #231846

    Hi,

    I created 2 tables part_sample and order_part and used foreign key constraint with default value. But I am getting error when I am deleting a row from part_sample . Can you please help me. Here are the table scripts

    CREATE TABLE part_sample

    (part_nmbr int PRIMARY KEY,

    part_name char(30),

    part_weight decimal(6,2),

    part_color char(15) );

    insert part_sample values (1,'monitor',20,'black')

    insert part_sample values (2,'cpu',20,'silver')

    insert part_sample values (3,'mouse',10,'grey')

    CREATE TABLE order_part

    (order_nmbr int,

    part_nmbr int default 0

    FOREIGN KEY REFERENCES part_sample(part_nmbr)

    ON DELETE SET DEFAULT,

    qty_ordered int);

    GO

    insert order_part values (1,1,20)

    insert order_part values (2,2,30)

    insert order_part values (3,3,40)

    Then, when I execute the following statement, it gets error.

    I want to have default values in order_part when a row is deleted in part_sample

    --delete from part_sample where part_nmbr = 2

  • January 7, 2011 at 5:35 am

    #1270574

    The error is exactly what the constraint is supposed to do, because it checks to see if there are children records before deleting. If you want to change those records to a default value, you'll need to use triggers or change the children records to a default value before you execute the delete statement.

    --------------------------------------------------------------------------
    When you realize you've dug yourself into a hole....Step 1...stop digging.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply