Re: Non-primary key constraints in Oracle

On Thu, 25 Jun 1998 01:55:44 GMT, Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>A copy of this was sent to joelga_at_pebble.org (Joel Garry)
>(if that email address didn't require changing)
>On Wed, 24 Jun 1998 22:08:15 GMT, you wrote:
>
>>On Tue, 23 Jun 1998 18:50:26 +0200, Rainer Scheel <rainer.scheel_at_sno.drs1.x400.sni.de> wrote:
>>>A foreign key constraint must reference a primary/unique key in the referenced
>>>table.
>>>Otherwise, you get
>>>ORA-02270: no matching unique or primary key for this column-list
>>>"Model" is not unique in table "Car", so in fact you cannot create a foreign key.
>>>(The reason is performance, I believe:
>>>You could create an index on "model" in table "car", but the RDBMS might not be
>>>handle the duplicates that efficient.)
>>
>>The docs keep telling me this, but I keep getting 2270 with just a unique key.
>>Is it supposed to be "unique AND primary key" or am I missing something else?
>>No problems with primary. 7.3.4 on Solaris 2.6.
>>
>>jg

Ah, all is now clear. It does say a unique constraint in the docs. 2270 is a trifle misleading.

>
>do you have a unique CONSTRAINT on the table or just a unique index. the index
>is not good enough (in fact, unique indexes aren't even documented :)...

I see references to CREATE UNIQUE INDEX syntax in both 7 (Administrators Guide p. 8-38) and 8 (SQL Ref)?

>
>See the following example that shows a unique index doesn't work but the unique
>constraint (which will 'take over' the unique index -- it won't recreate it)
>does..
>
>SQL> create table p ( x int );
>Table created.
>
>SQL> create unique index p_idx on p(x);
>Index created.
>
>SQL> create table c ( y int references p(x) );
>create table c ( y int references p(x) )
> *
>ERROR at line 1:
>ORA-02270: no matching unique or primary key for this column-list
>
>
>SQL> alter table p add constraint p_unique unique (x);
>Table altered.
>
>SQL> create table c ( y int references p(x) );
>Table created.
>
>
>
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>Herndon VA
>
>http://govt.us.oracle.com/ -- downloadable utilities
>
>----------------------------------------------------------------------------
>Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
>Anti-Anti Spam Msg: if you want an answer emailed to you,
>you have to make it easy to get email to you. Any bounced
>email will be treated the same way i treat SPAM-- I delete it.

--
These opinions are my own and not necessarily those of Information Quest or Pebble In The Sky http://www.informationquest.com mailto:jgarry@nospameiq.com http://ourworld.compuserve.com/homepages/joel_garry Remove nospam to reply. mailto:joel_garry_at_compuserve.nospam.com "See your DBA?" I AM the @#%*& DBA! Received on Thu Jun 25 1998 - 19:15:11 CDT