Re: Foreign Key with an "OR" condition (and two concatenated columns)?

On Jan 8, 2012, at 9:33, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:

> Hi. Hope I'm articulating the question correctly.
>
> I currently have a foreign key like this:
>
> "fk_table2" FOREIGN KEY (id) REFERENCES table1(id) ON DELETE CASCADE;
>
> Given the peculiar needs of this db, it seems that in some cases we
> will need to track the "id" of table2 against a concatenation of two
> columns in table2. So in pseudocode, it would look something like
> this.
>
> "fk_table2" FOREIGN KEY (id) REFERENCES table1(id OR
> id||'.'||column2) ON DELETE CASCADE;
>
> Do I need a separate constraint for this?
>
> Many thanks for any pointers!
>
>

Assuming you actually tried your syntax and received an error...

You cannot have two mutually exclusive constraints since one would always fail. You need to modify you schema to conform to the definition of a FOREGIN KEY or maintain integrity via a trigger.

Not enough details to comment on whether what you think you need is indeed a good design choice.

Database needs are nice in that they can be changed without much argument from the database. Now, if other developers exhibit these needs then your work becomes more difficult.

David J.