How to create relationship metadata between tables without a foreign key check?

Question

I use database metadata (primary keys, foreign keys, table names, etc), to generate code and some very complex queries. This metadata resides in theINFORMATION_SCHEMA. I am having problems with following (simplified) use case:

Tables

table A (with ID)
table B (with ID, ColumnA, ColumnC)
table C (with ID)

All ID Columns are unique, ColumnA and ColumnC have duplicate values

Situation

ColumnC has a subset of values from C, in other words, B will never have a value that C doesn't.

ColumnA has a non-perfect superset of values from A, in other words, B has exclusive values and almost all values of A, but A also rarely has exclusive values.

Problem

There is a Foreign Key between B.ColumnC referencing C.ID, which works as intended.

Now, because of the nature of Foreign Keys, there is no Foreign Key on B.ColumnA referencing A.ID, because B has values that A don't.

Question

Basically, I am having problem with some "many to many"-like relationships.

I thought about disabling Foreign Key checks and then creating a Foreign Key on B.ColumnA, but I haven't found a way to disable checks on only one specific contraint, because I still want to maintain data integrity on B.ColumnC Foreign Key.

Is there a way to create a relationship between B and A without losing data integrity and preferably without creating any additional tables or columns?

Edit: I am working with Postgres, but I will tag this with "database agnostic" to see if solutions from other vendors are portable to my case.

Answer 1

Adding a new table and proper foreign keys will likely be the more natural way to solve this.

In some DBMS (eg SQL SQL Server), you can create a disabled or disable an existing foreign key constraint with ALTER TABLE .. NOCHECK CONSTRAINT:

ALTER TABLE b NOCHECK CONSTRAINT [a_b_fk] ;

In Postgres, if you really don't want or not allowed to change the schema, you could create a foreign key from A to B and then disable the internal triggers associated with it. There is no way (as far as I know) to mark the foreign key constraint as "disabled" but disabling the internal triggers will have the desired effect.

Create the tables

blue=# create table a (id int primary key) ;
CREATE TABLE
blue=# create table b (id int primary key, aid int not null) ;
CREATE TABLE

Add the foreign key constraint

blue=# alter table b add constraint a_b_fk foreign key (aid) references a(id) ;
ALTER TABLE

Find the trigger names

blue=# SELECT tgname, tgconstraint
FROM   pg_trigger
WHERE  tgrelid = 'public.a'::regclass; 
             tgname             | tgconstraint 
--------------------------------+--------------
 RI_ConstraintTrigger_a_3657774 |      3657773
 RI_ConstraintTrigger_a_3657775 |      3657773
(2 rows)

blue=# SELECT tgname, tgconstraint
FROM   pg_trigger
WHERE  tgrelid = 'public.b'::regclass; 
             tgname             | tgconstraint 
--------------------------------+--------------
 RI_ConstraintTrigger_c_3657776 |      3657773
 RI_ConstraintTrigger_c_3657777 |      3657773
(2 rows)

Disable the triggers

blue=# alter table a disable trigger "RI_ConstraintTrigger_a_3657774"  ;
ALTER TABLE
blue=# alter table a disable trigger "RI_ConstraintTrigger_a_3657775"  ;
ALTER TABLE
blue=# alter table b disable trigger "RI_ConstraintTrigger_c_3657776"  ;
ALTER TABLE
blue=# alter table b disable trigger "RI_ConstraintTrigger_c_3657777"  ;
ALTER TABLE
blue=# 

we can now insert values as we please, the FOREIGN KEY isn't checked

blue=# insert into a (id) values (1), (2), (3) ;
INSERT 0 3
blue=# insert into b (id, aid) values (1,1), (2,1), (4,4) ;
INSERT 0 3

Show the tables' structure

blue=# \d a
                 Table "public.a"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "b" CONSTRAINT "a_b_fk" FOREIGN KEY (aid) REFERENCES a(id)
Disabled internal triggers:
    "RI_ConstraintTrigger_a_3657774" AFTER DELETE ON a FROM b NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del"()
    "RI_ConstraintTrigger_a_3657775" AFTER UPDATE ON a FROM b NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"()

blue=# \d b
                 Table "public.b"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 aid    | integer |           | not null | 
Indexes:
    "b_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "a_b_fk" FOREIGN KEY (aid) REFERENCES a(id)
Disabled internal triggers:
    "RI_ConstraintTrigger_c_3657776" AFTER INSERT ON b FROM a NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"()
    "RI_ConstraintTrigger_c_3657777" AFTER UPDATE ON b FROM a NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_upd"()

blue=#