| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Non-primary key constraints in Oracle
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
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 :)...
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) )
*
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
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. Received on Wed Jun 24 1998 - 20:55:44 CDT
 |
 |