Post reply

Composite Foreign Keys?

  • October 26, 2011 at 10:25 am

    #262090

    Hi All,

    I have a Primary Table called 'STATES' with primary keys (stateid) as below:

    stateid state_abb state_txt

    2AL ALABAMA

    3AK ALASKA

    4AZ ARIZONA

    5AR ARKANSAS

    6CA CALIFORNIA

    7CO COLORADO

    8CT CONNECTICUT

    and I have a Secondary Table called 'Subject' with multiple state columns.

    Home_State_id

    Birth_State_id

    Driver_License_State_id

    I need to know how to set up Primary and Foreign Key relationship on these 2 tables? is there any way to setup composite foreign key(single column primary key and multiple columns foreign key)?

  • October 26, 2011 at 10:38 am

    #1399775

    #1 I wouldn't bother with the identity here. Abbreviation is a correct PK.

    #2 You need 1 FK for each column in the 2nd table.

  • October 26, 2011 at 10:45 am

    #1399779

    Ninja's_RGR'us (10/26/2011)

    #1 I wouldn't bother with the identity here. Abbreviation is a correct PK.

    #2 You need 1 FK for each column in the 2nd table.

    Thanks Ninja's for a quick reply...

    OK 1 FK for each column!

    As I have to setup foreignkeys ON DELETE CADCADE...

    I get the following error...

    "Introducing FOREIGN KEY constraint 'FK74988DB24B3C886' on table 'Subjects' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

  • October 26, 2011 at 10:50 am

    #1399786

    I wouldn't bother with cascade here.

    States usually don't go anywhere (too often).

    And delete subjects shouldn't require you to delete a state.

    The problem probably that you have a circular refference.

    Delete a => delete b => goes back to delete a.

    The server stops that from hapenning with the error you just posted.

    I've always found that this is error is coming from bad table design. As with any generalisations, there should be exceptions!

  • October 26, 2011 at 12:12 pm

    #1399839

    Thanks Ninja...

    I have 50GB database without referrential integrity and lot's of table is having the same issue.

    So, basically there is no solution... PK and FK should have same numbers of columns to setup RI constraint.

    I can only create 1 FK constraint for instance -- State.stateid = Subjects.home_state_id with ON DELETE CASCADE

    and I have to leave the other columns without setting up Referrential Integrity.

  • October 26, 2011 at 1:49 pm

    #1399899

    In house app or 3rd party?

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

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