#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    Foreign key behavior PostgreSQL & MySQL


    Say I have these two tables:

    create table foo (
    f1 int2 primary key,
    f2 int2,
    f3 int2
    );

    create table bar (
    f1 int2,
    f2 int2,
    primary key (f1, f2)
    );

    As someone new to PostgreSQL coming from a MySQL background, I try and put a foreign key like so:
    alter table foo add foreign key (f2) references bar (f1);

    However, unlike MySQL, PostgreSQL doeesn't allow me to create this key as it restricts foreign keys to referencing only unique fields, which there is no guarantee that bar.f1 is unique.

    I am wondering what the PostgreSQL way for ensuring that the only values of foo.f2 that can be inserted are values that already exist in bar.f1.

    Thanks

    Note: I am thinking that perhaps a trigger that checks in the bar table to see if the value you are trying to insert into foo.f2 is in bar.f1 or not. But I'm not sure if this is the best way in PostgreSQL.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by niclas1047
    However, unlike MySQL, PostgreSQL doeesn't allow me to create this key as it restricts foreign keys to referencing only unique fields, which there is no guarantee that bar.f1 is unique.
    All other DBMS behave the way Postgres behaves (in my opinion this is a bug in MySQL)

    A foreign by definition references the primary key (or a unique key).

    Take this data in bar:

    Code:
     f1 |  f2
    ----|----
     1  |  1
     1  |  2
     1  |  3
    Now which row should the FK constraint point to?

    It makes absolutely no sense to reference only part of a primary key in the foreign key definition.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0
    I don't disagree with you. It doesn't make sense the way MySQL does allow you to do this.

    So how would you go about ensuring referential integrity in this case in PostgreSQL? Triggers and procedures?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    If you have a value that is recurring then it should be split off as its own table. I have a strong suspicion that you are falling into to the X Y problem trap.

    Tell us more about the situation -- the context of what you are trying to do -- and then better advice will be forthcoming. I am pretty sure it will involve insight into schema design instead of mere technical advice on how to set up a trigger that scans a table (which is enormously wasteful on large tables...).

IMN logo majestic logo threadwatch logo seochat tools logo