Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Unanswered: Referential integrity with no foreign key?

    Hi,

    I have two tables

    TABLE 1

    table1_id - primary key
    nice_desc

    TABLE2
    table2_id - primary key
    nice_desc


    Now, customer wants to ensure that values entered into the table2 column for nice_desc already exist in the TABLE1.

    This isn't the seti I would like to have here .. was already implemented. I would like to have just zapped the nice_desc column from table2 and put table1_id there instead and then made table2.table1_id a foreign key. But I'm stuck .. this is an issue that extends the entire model.

    I quess my question is .. is there any way to enforce referentical integrity and prevent redundancy without using triggers?? Or foreign keys? I don't think there is ..
    I'm working on DB2. Have tried the check constraint way .. but don't think this is an option.

    ALTER TABLE table1 ADD CONSTRAINT "table1_ch3"
    CHECK (column in (select column from table2))
    ;

    Any DB2 gurus out there?? Although I don't think that there is any nice solution to this .. other than redesign.

    Thanks

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: Referential integrity with no foreign key?

    Hi,

    you have got 3 options:

    a) foreign keys
    b) triggers
    c) application logic

    a) is best, and c is worst, of course.

    Johann

    Originally posted by maeveb
    Hi,

    I have two tables

    TABLE 1

    table1_id - primary key
    nice_desc

    TABLE2
    table2_id - primary key
    nice_desc


    Now, customer wants to ensure that values entered into the table2 column for nice_desc already exist in the TABLE1.

    This isn't the seti I would like to have here .. was already implemented. I would like to have just zapped the nice_desc column from table2 and put table1_id there instead and then made table2.table1_id a foreign key. But I'm stuck .. this is an issue that extends the entire model.

    I quess my question is .. is there any way to enforce referentical integrity and prevent redundancy without using triggers?? Or foreign keys? I don't think there is ..
    I'm working on DB2. Have tried the check constraint way .. but don't think this is an option.

    ALTER TABLE table1 ADD CONSTRAINT "table1_ch3"
    CHECK (column in (select column from table2))
    ;

    Any DB2 gurus out there?? Although I don't think that there is any nice solution to this .. other than redesign.

    Thanks

Posting Permissions

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