0

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.

  • Would it make sense to create a foreign key from A (id) to B (columnA)? Does B (columnA) have unique values? – ypercubeᵀᴹ Nov 14 '18 at 16:00
  • @ypercubeᵀᴹ Thank you for your input. Your approach makes perfect sense, I will try it and see if I can take this approach. And yes, B.ColumnAhas duplicates while A.ID has unique values, I will update my question accordingly – LouizFC Nov 14 '18 at 16:26
  • You haven't told us the specifics of your case. But it almost sounds as if the relationship between table B and table C is of the HAS-A variety, while the relationship between table A and table B is of the IS-A variety. – Walter Mitty Nov 14 '18 at 16:29
  • @WalterMitty I am sorry that I can't talk about the specifics. Also I tried ypercubeᵀᴹ suggestion but I found new information, I updated my question accordingly (see the Situation section) – LouizFC Nov 14 '18 at 16:45
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=# 
  • There's probably a clever way to identify the 4 triggers associated with a specific foreign key. I'll add an edit if I find it. (because you might have many FKs from a table and you only want to disable one of them) – ypercubeᵀᴹ Nov 16 '18 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.