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.
Best Answer
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
:In Postgres, if you really don't want or not allowed to change the schema, you could create a foreign key from
A
toB
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
Add the foreign key constraint
Find the trigger names
Disable the triggers
we can now insert values as we please, the FOREIGN KEY isn't checked
Show the tables' structure