Re: Foreign key to subset of records

On Sun, 15 Apr 2001 14:02:04 -0400, Evgeny Sorokin <SSEEAA_at_MailAndNews.com> wrote:

>May be it is a question, may be suggestion to Oracle.
>I want to create foreign key like this:
>
>alter table <table1> add
> constraint <constraint> foreign key (<field1>,<constant>)
> references <master-table> (<field2>,<field3>);
>
>Oracle must execute SQL like
>
> select ... from <master-table>
> where <field2>=:param1 and <field3>=<constant>
>
>on inserting or updating value of <table1>.<field1>
>
>In this case primary (or unique) key on <master-table> can be
>(<field2>,<field3>) or (<field2>).
>
>May be such option already exists in newest versions like 8.1.6 or 8.1.7?
>Thank you

This is definitely violating relational theory, so I don't think Oracle will ever implement it. Microsux and MySql may decide to do so, but Oracle : I don't think so
And of course the solution to your problem is quite easy, you don't even need the constant when you add a (according to you) redundant column and simply assign
default <your constant>
You could still question whether that's appropiate relational design, but it does suit your need.

Hth,

Sybrand Bakker, Oracle DBA Received on Sun Apr 15 2001 - 15:44:38 CDT