Results 1 to 3 of 3
  1. #1
    Join Date
    May 2011
    Posts
    7

    ER design problem

    Hello Guy

    I have three tables where table1 has 1 to many relationship to table 2.

    i.e

    table1 (table1_id, table1_name)
    table2 (table2_id, table2_name, table1_id)

    table3 has a m-to-m relation to table2, But I want to enforce in this relation that table1_id and table3_id is unique

    i.e
    table3 (table3_id, name)
    table2_table3 (table3_id, table2_id, table1_id,
    unique (table3_id,table1_id ) )

    In the above there is a design flow because table1_id depends on table2_id and that means redundant data and may be inconsistent.

    Also, I can do the design like this and use triggers to insure the uniqueness constraint.
    table2_table3 (table3_id, table2_id)


    My question, Is there is another design I am missing or option

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    You can add a redundant UNIQUE constraint to table2:

    Code:
    alter table table2 add constraint table2_uniq (table2_id, table1_id)
    I say "redundant" because of course (table2_id) is the primary key and so (table2_id, anything) is trivially unique too. But now you can do this:

    Code:
    table2_table3 (table3_id, table2_id, table1_id,
    unique (table3_id,table1_id),
    foreign key (table2_id, table1_id) references table2 (table2_id, table1_id) )
    This will enforce the uniqueness and consistency of the data in table2_table3.

  3. #3
    Join Date
    May 2011
    Posts
    7
    Thanks, This can solve it

    Regards

Posting Permissions

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