June 15th, 2013, 09:46 AM
Foreign key behavior PostgreSQL & MySQL
Say I have these two tables:
create table foo (
f1 int2 primary key,
create table bar (
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.
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.
June 15th, 2013, 10:17 AM
All other DBMS behave the way Postgres behaves (in my opinion this is a bug in MySQL)
Originally Posted by niclas1047
A foreign by definition references the primary key (or a unique key).
Take this data in bar:
Now which row should the FK constraint point to?
f1 | f2
1 | 1
1 | 2
1 | 3
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.
Tips on how to ask better questions:
June 15th, 2013, 10:42 AM
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?
June 26th, 2013, 08:57 PM
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...).