Postgresql – check key if exists in other table without fk constraint

constraintpostgresqlpostgresql-9.2

Lets say I have:

table a (id_a int);
table b (id_b int, id_a int);

I want to check if b.id_a exists in "a" before a new row in "b" will be inserted. Normally I would just add a fk constraint on b.id_a. The problem is that I'm not allowed to add a pk or uq constraint to "a.id_a" to reference the fk on b.id_a.

Would be nice if somebody has a solution for me.

Edit:
Why not fk constraint – My model consists of an ESRI Arc SDE with spatial tables and simple postgres tables. Some pg tables reference to an sde uq identifier column. By default the SDE adds no pk to a spatial table in postgres (in sql server it does dont ask me why). However if a pk would be added by default by the SDE it wont help me at all, because i would have to change it to an other uq column, because the default pk column keys could change in the future (=WTF). ATM i just added or changed the pk of the SDE table to my uq identifier column, but as i had errors at first when restoring the db (now it works for the moment), ESRI support told me that my change or add of the pk column can cause these errors or maybe cause some in the future (because SDE wont know this pk). In my opinion it the pk should not matter (because SDE wont know and also wont use it) that's why i added it atm and it seems to work. But for the future, i want to be on the safe side and so i have to find an other possibility.

What i want: It would be nice to have the same behaviour like with a fk constraint – but i guess a similar behaviour wont be possible because that's what fks made for.

I have tried to write a before insert trigger but i think its the wrong approach as it seems that i always have to return the new.row and i don't want something to be inserted when the check for the value to be inserted don't exists.

I also could simply use no relationship or check on the fk but this in my opinion would be a high risk for having wrong values in the tbl.

Thanks for helping me…

Possible Solution:

I have tried to add a fk constraint which references on a column with uq index and it works (thx to ypercube). I have taken a look at the doc, but i cant find something about adding a fk constraint referencing on a column with just a uq index (no pk or uq constraint). At least i found a possible explanation in a tutorial which says

A unique constraint is actually implemented as a unique index in PostgreSQL, just as it is in many databases.

but also

This index wouldn’t be a good candidate for a foreign key, but it does illustrate how you can create a unique index that you can reference with a foreign key.

I just don't really get it why it shouldn't be a "good candidate" for a fk constraint, when either using a uq constraint or uq index on that column grants you unique values.

from docs pg uq constraint:

Unique constraints ensure that the data contained in a column or a group of columns is unique with respect to all the rows in the table.

from docs pg uq index:

Causes the system to check for duplicate values in the table when the index is created (if data already exist) and each time data is added. Attempts to insert or update data which would result in duplicate entries will generate an error.

Best Answer

I do agree with Aaron, that not creating a PK (or unique constraint) is a very strange requirement

But you could do something like this:

insert into b (id_b, id_a)
select 1, 2 
where exists (select 1 from a where id_a = 2);

or alternatively:

insert into b (id_b, id_a)
select 1, id_a 
from a where id_a = 2

If you want to insert multiple rows, you can use a values clause:

insert into b (id_b, id_a)
select * 
from (
   values 
      (1,2), (2,3), (3,4)
) as t(id_b, id_a) 
where exists (select 1  
              from a 
              where a.id_a = t.id_a);

This will only make sure that this specific insert will insert correct data. It will not prevent others that don't follow this pattern to insert invalid data. And it is not safe in a multi-user environment where different transactions run the inserts concurrently.

Related Solutions

Sql-server – Unable to drop non-PK index because it is referenced in a foreign key constraint

Because a foreign key can point to a primary key or a unique constraint, and whoever created that foreign key possibly created it before the primary key existed (or they shifted the FK to point to the Unique index while they changed something else about the primary key). This is easy to repro:

CREATE TABLE dbo.MyTable(MyTableID INT NOT NULL, CONSTRAINT myx UNIQUE(MyTableID));

CREATE TABLE dbo.OtherTable1(ID INT FOREIGN KEY REFERENCES dbo.MyTable(MyTableID));

ALTER TABLE dbo.MyTable ADD CONSTRAINT PKmyx PRIMARY KEY(MyTableID);

CREATE TABLE dbo.OtherTable2(ID INT FOREIGN KEY REFERENCES dbo.MyTable(MyTableID));

In fact, both of these foreign keys will point to the first unique constraint defined on that column (myx).

You can fix the foreign key on the other table by dropping it and re-creating it. You will need to repeat that process for any other tables that point to this column. You can find these easily:

SELECT s.name,t.name,fk.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk
ON fkc.constraint_object_id = fk.[object_id]
INNER JOIN sys.tables AS t
ON fkc.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns AS c1
ON c1.[object_id] = fkc.referenced_object_id
AND c1.column_id = fkc.referenced_column_id
AND c1.name = N'MyTableID'
WHERE fkc.referenced_object_id = OBJECT_ID('dbo.MyTable');

Results:

dbo    OtherTable1    FK__OtherTable1__ID__32E0915F
dbo    OtherTable2    FK__OtherTable2__ID__35BCFE0A

And even generate a script to drop and re-create them (dropping the redundant unique constraint in the meantime):

DECLARE 
  @sql1 NVARCHAR(MAX) = N'', 
  @sql2 NVARCHAR(MAX) = N'ALTER TABLE dbo.MyTable DROP CONSTRAINT myx;', 
  @sql3 NVARCHAR(MAX) = N'';

SELECT 
  @sql1 += N'
ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';',
  @sql3 += N'
ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY '
  + '(' + QUOTENAME(c2.name) + ') REFERENCES dbo.MyTable(MyTableID);'
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk
ON fkc.constraint_object_id = fk.[object_id]
INNER JOIN sys.tables AS t
ON fkc.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns AS c1
ON c1.[object_id] = fkc.referenced_object_id
AND c1.column_id = fkc.referenced_column_id
AND c1.name = N'MyTableID'
INNER JOIN sys.columns AS c2
ON c2.[object_id] = fkc.parent_object_id
AND c2.column_id = fkc.parent_column_id
WHERE fkc.referenced_object_id = OBJECT_ID('dbo.MyTable');

PRINT @sql1;
PRINT @sql2;
PRINT @sql3;
-- EXEC sp_executesql @sql1;
-- EXEC sp_executesql @sql2;
-- EXEC sp_executesql @sql3;

Results:

ALTER TABLE [dbo].[OtherTable1] DROP CONSTRAINT [FK__OtherTable1__ID__32E0915F];
ALTER TABLE [dbo].[OtherTable2] DROP CONSTRAINT [FK__OtherTable2__ID__35BCFE0A];

ALTER TABLE dbo.MyTable DROP CONSTRAINT myx;

ALTER TABLE [dbo].[OtherTable1] ADD CONSTRAINT [FK__OtherTable1__ID__32E0915F] 
  FOREIGN KEY ([ID]) REFERENCES dbo.MyTable(MyTableID);
ALTER TABLE [dbo].[OtherTable2] ADD CONSTRAINT [FK__OtherTable2__ID__35BCFE0A] 
  FOREIGN KEY ([ID]) REFERENCES dbo.MyTable(MyTableID);

This explicitly handles this case, where the constraint only involves a single column. It gets a little more complex if there are multiple columns involved (and this answer is not meant to solve that problem). I also didn't test if this works exactly as coded if the foreign keys point to a redundant unique index (which has the same underlying structure but is created with slightly different DDL). Exercise for the reader. :-)

PostgreSQL CHECK constraint is evaluated wrongly

Well, your query here:

return NOT Exists (
    Select 1 
    FROM example_table AS a, example_table AS b 
    WHERE a.id != b.id AND a.value = b.value);

is not going to see the not-yet-inserted row, which is why it returns true on this second INSERT in your example:

insert into example_table (id, value) values (1,0);

Now, you could almost fix this by hacking up the query to something more like:

CREATE OR REPLACE FUNCTION checkunique(in_value integer)
RETURNS boolean AS
$BODY$
begin
    RETURN NOT Exists (
    Select 1
    FROM example_table WHERE value = $1);
end
$BODY$
LANGUAGE plpgsql STABLE;

ALTER TABLE example_table add constraint unique_value check (checkunique(value));

However, this is really not what CHECK constraints are supposed to be used for, and it actually introduces a race condition if you have multiple transactions writing to example_table at the same time (can you see how?). Use the UNIQUE constraints that PostgreSQL provides. If your values are too large for the UNIQUE constraint's B-Tree index, create the UNIQUE constraint on an MD5() of the value.

Best Answer

Related Solutions

Sql-server – Unable to drop non-PK index because it is referenced in a foreign key constraint

PostgreSQL CHECK constraint is evaluated wrongly

Related Question