CODEWITHSUNDEEP
postgresql
vara
vara

Reputation: 836

Need foreign key as array

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.

Upvotes: 2

Views: 11090

Answers (3)

Rinat
Rinat

Reputation: 648

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

Upvotes: 3

P3k
P3k

Reputation: 11

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();

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78473

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.

Upvotes: 6

Related Questions