Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Arrow Unanswered: [newbie] Foreign Key and Check Constraint

    Hi,

    I have a simple question.

    Two tables:

    CREATE TABLE table1 (
    Attr1 VARCHAR(2) NOT NULL WITH DEFAULT 'XY',
    Attr2 INTEGER NOT NULL
    );
    CREATE UNIQUE INDEX table1_UI ON table1 ( Attr1 ) PCTFREE 0;
    ALTER TABLE table1 ADD CONSTRAINT table1_PK PRIMARY KEY ( Attr1 );
    ALTER TABLE table1 ADD CONSTRAINT table1_CK CHECK (Attr1 in ( 'XY', 'AB', 'CD' ));

    CREATE TABLE table2 (
    Attr1 VARCHAR(2) NOT NULL.
    Attr3 INTEGER NOT NULL,
    Attr4 INTEGER NOT NULL,
    );
    CREATE UNIQUE INDEX table2_UI ON table2 ( Attr1, Attr3 ) PCTFREE 0;
    ALTER TABLE table2 ADD CONSTRAINT table2_PK PRIMARY KEY ( Attr1, Attr3 );
    ALTER TABLE table2 ADD FOREIGN KEY table2_FK (Attr1) REFERENCES table1 (Attr1) ON DELETE NO ACTION;

    Is it necessary to add the same CHECK CONSTRAINT and WITH DEFAULT 'XY' to the second table or are this properties automatically induced by the the first table?

    Appreciate everyones help.

    Thanks in Advance,

    S.B.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you want the default of Attr1 on Table2 to be 'XY' then you will have to specify that default on the column definition of Attr1 for Table2. But a CHECK CONSTRAINT on table2 like the one on table1 would be redundant since that is handled by the RI.

    Note that even if the default is assigned to Attr1 on table1 and table2, a parent row for 'XY’ must exist on table1 for the RI constraint on table2 to be valid if it defaulted to 'XY'.

  3. #3
    Join Date
    Oct 2003
    Posts
    18
    Thanks a lot!

    Assumed, there is a third table like this:

    CREATE TABLE table3 (
    Attr1 VARCHAR(2) NOT NULL WITH DEFAULT 'XY'.
    Attr3 INTEGER NOT NULL,
    Attr5 INTEGER NOT NULL,
    Attr6 INTEGER NOT NULL,
    );
    CREATE UNIQUE INDEX table3_UI ON table3 ( Attr1, Attr3, Attr5 ) PCTFREE 0;
    ALTER TABLE table3 ADD CONSTRAINT table3_PK PRIMARY KEY ( Attr1, Attr3, Attr5 );
    ALTER TABLE table3 ADD FOREIGN KEY table3_FK (Attr1, Attr3) REFERENCES table2 (Attr1, Attr3) ON DELETE NO ACTION;

    In this case table1 and table3 are not directy connected... Is this construction valid without any additional check constraints?

    Originally posted by Marcus_A
    If you want the default of Attr1 on Table2 to be 'XY' then you will have to specify that default on the column definition of Attr1 for Table2. But a CHECK CONSTRAINT on table2 like the one on table1 would be redundant since that is handled by the RI.

    Note that even if the default is assigned to Attr1 on table1 and table2, a parent row for 'XY� must exist on table1 for the RI constraint on table2 to be valid.
    Last edited by stefanB; 10-14-03 at 05:40.

Posting Permissions

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