Need foreign key as array

Question

CREATE TABLE test ( id int PRIMARY KEY , name );

CREATE TABLE test1 ( id integer[] REFERENCES test , rollid int );

ERROR: foreign key constraint "test3_id_fkey" cannot be implemented DETAIL: Key columns "id" and "id" are of incompatible types: integer[] and integer.

after that I try to another way also

CREATE TABLE test1 ( id integer[] , rollid int);

ALTER TABLE test1 ADD CONSTRAINT foreignkeyarray FOREIGN KEY (id) REFERENCES test;

ERROR: foreign key constraint "fkarray" cannot be implemented DETAIL: Key columns "id" and "id" are of incompatible types: integer[] and integer.

so I try create a foreign key array means it say error. please tell me anyone?

postgresql version is 9.1.

Fundamentally this is an incorrect use of arrays - if you have a one-to-many relationship, you should model it with an intermediary table with foreign keys to the two "real" tables. — IMSoP, Nov 26 '13 at 13:15
In fact, a patch exists for this (never tested), see [Patch: Array ELEMENT Foreign Keys](https://commitfest.postgresql.org/action/patch_view?id=900) and [the blog of one of its authors](http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/) — julp, Nov 26 '13 at 16:13

score 6 · Answer 1 · answered Nov 26 '13 at 11:26

6

What you're trying to do simply can't be done. At all. No ifs, no buts.

Create a new table, test1_test, containing two fields, test1_id, test_id. Put the foreign keys as needed on that one, and make test1's id an integer.

answered Nov 26 '13 at 11:26

Denis de Bernardy

75,850
13
131
154

I just told you: not possible. Not the way you're doing it, anyway. You'd need the array to reference another array. But then, even with an odd unique btree on it, you'd have no guarantee that the array would be in the correct order without further constraints and such. So really, not a good option in practice either. (Fwiw, I've tried this myself a few years back. Just forget it: you don't want this.) – Denis de Bernardy Nov 26 '13 at 11:36
A last option would be to create triggers on test1 that unnest the array to make the check for each id in test. And yet more triggers on test that check and update the arrays from test as relevant. Trust me here: it's nuts. The *only* sane solution is to normalize this the way it should be, with a separate test1_test table. – Denis de Bernardy Nov 26 '13 at 11:40
@DenisdeBernardy How would it be done with a trigger? I'm looking to store child objects, but they have to be in a certain order (so a join table wouldn't be sufficient). – jhpratt Feb 03 '19 at 03:20
@jhpratt: Per my earlier comments you most definitely don't want to mess around with foreign key constraints on arrays. Add an integer or float field to the join table to track the order instead. – Denis de Bernardy Feb 04 '19 at 04:44

Rinat · Answer 2 · 2020-10-27T13:06:41.100

Using arrays with foreign element keys is usually a sign of incorrect design. You need to do separate table with one to many relationship.

But technically it is possible. Example of checking array values without triggers. One reusable function with paramethers and dynamic sql. Tested on PostgreSQL 10.5

create schema if not exists test;

CREATE OR REPLACE FUNCTION test.check_foreign_key_array(data anyarray, ref_schema text, ref_table text, ref_column text)
    RETURNS BOOL
    RETURNS NULL ON NULL INPUT
    LANGUAGE plpgsql
AS
$body$
DECLARE
    fake_id text;
    sql text default format($$
            select id::text
            from unnest($1) as x(id)
            where id is not null
              and id not in (select %3$I
                             from %1$I.%2$I
                             where %3$I = any($1))
            limit 1;
        $$, ref_schema, ref_table, ref_column);
BEGIN
    EXECUTE sql USING data INTO fake_id;

    IF (fake_id IS NOT NULL) THEN
        RAISE NOTICE 'Array element value % does not exist in column %.%.%', fake_id, ref_schema, ref_table, ref_column;
        RETURN false;
    END IF;

    RETURN true;
END
$body$;

drop table if exists test.t1, test.t2;

create table test.t1 (
    id integer generated by default as identity primary key
);

create table test.t2 (
    id integer generated by default as identity primary key,
    t1_ids integer[] not null check (test.check_foreign_key_array(t1_ids, 'test', 't1', 'id'))
);

insert into test.t1 (id) values (default), (default), (default); --ok
insert into test.t2 (id, t1_ids) values (default, array[1,2,3]); --ok
insert into test.t2 (id, t1_ids) values (default, array[1,2,3,555]); --error

score 0 · Answer 3 · answered Aug 25 '14 at 15:18

If you are able to put there just values from test.id, then you can try this:

CREATE OR REPLACE FUNCTION test_trigger() RETURNS trigger 
LANGUAGE plpgsql AS $BODY$ 
DECLARE 
val integer; 
BEGIN 
SELECT id INTO val 
  FROM (
       SELECT UNNEST(id) AS id
       FROM test1
       ) AS q
 WHERE id = OLD.id;

IF val IS NULL THEN RETURN OLD;
ELSE 
RAISE 'Integrity Constraint Violation: ID "%" in Test1', val USING ERRCODE = '23000';
    RETURN NULL;
END IF;
END; $BODY$;

-- DROP TRIGGER test_delete_trigger ON test;
CREATE TRIGGER test_delete_trigger BEFORE DELETE OR UPDATE OF id ON test
FOR EACH ROW EXECUTE PROCEDURE test_trigger();

The select can be simplified using `select ... from test1 where id = any(old.id)`. But you would need to use `count(*)` it `old.id` contains more than one value, the `select .. into` will fail — , Apr 21 '16 at 12:08

Need foreign key as array

3 Answers3

Linked