want to create a a foreign key constraint on a column with unique index but getting a 02270. the fk can have multiple entries, but must be unique on the referenced table. i get the error when the referenced has a unique index, but not when the referenced key has a unique constraint. i thought the unique constraint came with the unique index? No?
Before I go on, for all the oracle -error printers on here, please read the rest of this question before just putting up what a 2270 is. I know what a 2270 is.
testinstance11> create table test_cons1(
2 pk_col integer,
3 snap integer
4 );
Table created.
Elapsed: 00:00:00.00
testinstance11> alter table test_cons1 add constraint pk_field primary key(pk_col);
Table altered.
Elapsed: 00:00:00.00
testinstance11> create unique index idx_snap on test_cons1(snap);
Index created.
Elapsed: 00:00:00.00
testinstance11> create table test_cons2(snap integer);
Table created.
Elapsed: 00:00:00.00
testinstance11> alter table test_cons2 add (
2 constraint fk_snap
3 foreign key (snap)
4 references test_cons1 (snap)
5 );
references test_cons1 (snap)
*
ERROR at line 4:
ORA-02270: no matching unique or primary key for this column-list
Elapsed: 00:00:00.00
testinstance11> drop index idx_snap;
Index dropped.
Elapsed: 00:00:00.00
testinstance11> alter table test_cons1
2 add constraint unq_snap unique (snap);
Table altered.
Elapsed: 00:00:00.00
testinstance11> alter table test_cons2 add (
2 constraint fk_snap
3 foreign key (snap)
4 references test_cons1 (snap)
5 );
Table altered.
Elapsed: 00:00:00.00
testinstance11>
{code
This is telling me that a unique constraint creates an index, but a unique index doesnt create a constraint.