(Sorry for my bad english comments)
Demo sql:
BEGIN; -- Begins the magic
CREATE TABLE pk ( -- pk table
id BIGINT,
CONSTRAINT pk2_id PRIMARY KEY (id)
);
CREATE TABLE fk ( -- fk table
fk_ids BIGINT[]
);
CREATE FUNCTION bia2bi(bigint[]) RETURNS bigint -- temp type cast
AS $$
SELECT $1[array_lower($1, 1)];
$$
LANGUAGE sql;
CREATE FUNCTION bi_fk(bigint, bigint[]) RETURNS boolean --FK FUNCTION FOR
TABLE pk
AS $$
BEGIN
IF (SELECT count(*) FROM pk WHERE id = ANY($2)) = (array_upper($2, 1) -
array_lower($2, 1) + 1) THEN
RETURN TRUE;
END IF;
RAISE EXCEPTION 'NO FK![%, %]', $1, $2; -- RAISE ERROR MSG, and dont
scan other million records
END;
$$
LANGUAGE 'plpgsql';
CREATE OPERATOR ==> ( -- Cutsom operator for calling bi_fk
PROCEDURE = bi_fk,
LEFTARG = bigint,
RIGHTARG = bigint[]);
CREATE CAST (bigint[] AS bigint) -- TEMP CAST FOR INDEX CREATION
WITH FUNCTION bia2bi(bigint[])
AS IMPLICIT;
ALTER TABLE "fk" -- CREATE FK
ADD CONSTRAINT "fk_id" FOREIGN KEY ("fk_ids")
REFERENCES "pk"("id")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;
DROP CAST(bigint[] AS bigint); -- REMOVE CAST(recomended, but not needed)
UPDATE pg_constraint
SET conpfeqop = ARRAY(SELECT OID FROM pg_operator WHERE oprname = '==>')
WHERE conname = 'fk_id'; -- CHANGING COMPARE FUNCTION
INSERT INTO pk SELECT k FROM GENERATE_series(1, 100000) as k; -- MAKE DATA
INSERT INTO fk VALUES(ARRAY[1,2, 3]); -- TESTING
INSERT INTO fk VALUES(ARRAY[6,3,5]); -- TESTING
INSERT INTO fk VALUES(ARRAY[6,3,444444444444444445]); -- FK BREAK.
ROLLBACK; -- Magic rollback =)
