-2

I was trying to create an fk in oracle but ran into ORA-02270 problems. After some googling, I found the reason is that you can't create fk towards unique index. It need to be a unique constraint.

I also have read Oracle unique constraint and unique index and it explained well that index and constraint are different things.

What I don't understand is, WHY oracle doesn't allow creating fk on unique index? I think it's perfectly reasonable. Besides, I also tried Postgres and it has no problem of doing this.

lidlesseye
  • 165
  • 9
  • 1
    Only the Oracle product managers can answer that. But they do have a track record for preferring features that sound cool to managers, not those that would help developers. –  Mar 30 '20 at 06:34

2 Answers2

3

Constraints are part of the SQL standard & relational theory. Indexing is not. It could be part of the standard, but it isn't. But if it were part of the standard then the standard still shouldn't mix up two levels--abstract relation interface vs its implementation that it should be independent of. SQL says a FK (foreign key) references a UNIQUE or PK (primary key). So if the DBMS conforms, you have to do that. But when you declare a UNIQUE constraint the DBMS will give you an implicit unique index. (And when you declare a PK constraint the DBMS will give you an implicit UNIQUE NOT NULL constraint & implicit corresponding index.)

philipxy
  • 14,867
  • 6
  • 39
  • 83
1

Foreign key constraints always refer to the primary/unique constraint in the oracle.

If you see the view USER_CONSTRAINTS then you will see the R_CONSTRAINT_NAME for the foreign key constraint. it is PRIMARY/UNIQUE constraints name. It is not a unique index.

select * from USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R';

A unique index is an altogether different entity from the TABLE and as you must be aware that syntax of the INDEX is also not part of CREATE TABLE inline syntax.

This is how oracle operates and If you want to create the FK on the column on which UNIQUE index is created then I don't think you would mind creating the UNIQUE constraint on the column instead of the UNIQUE index (Oracle will create the UNIQUE index internally on the column on which a UNIQUE constraint is created. so both are the same) but Oracle must need the constraint to be placed in the R_CONSTRAINT_NAME for foreign key constraint.(simplified it using the example of the R_CONSTRAINT_NAME)

Popeye
  • 35,427
  • 4
  • 10
  • 31