Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02270 on FK creation attempt (Oracle 19c R2 Standard Edition 2 Linux)
ORA-02270 on FK creation attempt [message #686056] Sun, 05 June 2022 07:08 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi all,
I'm probably misunderstanding this error, but a bit stuck and would be happy for explanation..

I'm creating a parent table with a UNIQUE index

SQL>
SQL> create table T_PARENT ( COL_P NUMBER);

Table created.

SQL> CREATE UNIQUE INDEX T_UQ_IND1 ON T_PARENT ( COL_P);

Index created.

SQL>
And then a child table:

SQL> create table T_CHILD ( COL_C NUMBER);

Table created.

SQL>
When I try to add a Foreign Key on the child table to reference the parent table I get the ORA-02270:

SQL> ALTER TABLE T_CHILD ADD CONSTRAINT T_CHILD_FK_TO_T_PARENT  FOREIGN KEY ( COL_C) REFERENCES T_PARENT ( COL_P ) ;
ALTER TABLE T_CHILD ADD CONSTRAINT T_CHILD_FK_TO_T_PARENT  FOREIGN KEY ( COL_C) REFERENCES T_PARENT ( COL_P )
                                                                                                      *
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

I know that if I would add a Primary Key on T_PARENT ( COL_P) the FK will work.
However, if it says that there is "no unique key key matching" and I know that there is ( because I just created it a moment ago),
Why does it throw the error ?

Obviously, I'm misunderstanding something.. help please

Thanks,
Andrey

[Updated on: Sun, 05 June 2022 07:09]

Report message to a moderator

Re: ORA-02270 on FK creation attempt [message #686057 is a reply to message #686056] Sun, 05 June 2022 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
However, if it says that there is "no unique key key matching" and I know that there is ( because I just created it a moment ago),

You did not created an unique key, you created an unique index.
A key is a declarative constraint on a table.
An index is a physical object to faster access rows.
A key is part of the data model, an index is part of a physical implementation of this one.
A key may (or not) be implemented using an index; this is how Oracle chooses to efficiently verifies the key constraint but it is not mandatory.

So the solution to your issue is to create a unique constraint:
SQL> create table T_PARENT ( COL_P NUMBER);

Table created.

SQL> CREATE UNIQUE INDEX T_UQ_IND1 ON T_PARENT ( COL_P);

Index created.

SQL> create table T_CHILD ( COL_C NUMBER);

Table created.

SQL> alter table T_PARENT add constraint t_parent_unq unique (col_p);

Table altered.

SQL> ALTER TABLE T_CHILD ADD CONSTRAINT T_CHILD_FK_TO_T_PARENT  FOREIGN KEY ( COL_C) REFERENCES T_PARENT ( COL_P ) ;

Table altered.
You may then note that Oracle chose to use your index for the implementation:
SQL> select index_name from user_constraints where constraint_name=upper('t_parent_unq');
INDEX_NAME
------------------------------
T_UQ_IND1
It is not even mandatory for this index to be unique:
SQL> drop table T_CHILD;

Table dropped.

SQL> drop table t_parent;

Table dropped.

SQL> create table T_PARENT ( COL_P NUMBER);

Table created.

SQL> CREATE INDEX T_IND1 ON T_PARENT ( COL_P);

Index created.

SQL> alter table T_PARENT add constraint t_parent_unq unique (col_p);

Table altered.

SQL> select index_name from user_constraints where constraint_name=upper('t_parent_unq');
INDEX_NAME
------------------------------
T_IND1
If you have several possible indexes to implement the constraint you can even choose the index you want Oracle uses for the constraint with the USING INDEX clause (left as exercise Smile ).

Re: ORA-02270 on FK creation attempt [message #686058 is a reply to message #686056] Sun, 05 June 2022 09:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
There's some history behind this. B-tree indexes were introduced in (IIRC) release 6, and you could define them as unique or nonunique. A unique index was the closest we had to a primary key constraint back then, because Oracle did not have constraints until release 7.

Possibly due to this historical anomaly, I believe that there is a stupid optimization failure that persists to this day. Well, up to 19.3, I haven't tested subsequently. If you define a unique constraint and select with an equality predicate, there is always one extra block read if the index is nonunique rather than unique. I think this is because with a nonunique index, after finding the leaf block that has the key Oracle then looks at the next leaf block to see if the key is repeated there. Why? The constraint should tell it that the key cannot be repeated. You don't get the extra block read if the index is unique. This is irritating because in other ways (deferrable constraints are the most obvious) nonunique indexes are better to work with.

I have long believed (I am very much open to correction on this) that there is no difference between the structure of a unique and a nonunique index: that it is only a dictionary definition thing.
Re: ORA-02270 on FK creation attempt [message #686059 is a reply to message #686057] Mon, 06 June 2022 02:21 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
[quote title=Michel Cadot wrote on Sun, 05 June 2022 15:55]
Quote:


If you have several possible indexes to implement the constraint you can even choose the index you want Oracle uses for the constraint with the USING INDEX clause (left as exercise Smile ).


THANK YOU very much! Much appreciated.
Indeed works like magic:

SQL> create table T_PARENT ( COL_P NUMBER,COL_P2 NUMBER);

Table created.

SQL> CREATE UNIQUE INDEX T_UQ_IND1 ON T_PARENT ( COL_P);

Index created.

SQL>
SQL> create table T_CHILD ( COL_C NUMBER);

Table created.

SQL>
SQL>
SQL>
SQL>
SQL> ALTER TABLE T_CHILD ADD CONSTRAINT T_CHILD_FK_TO_T_PARENT  FOREIGN KEY ( COL_C) REFERENCES T_PARENT ( COL_P ) ;
ALTER TABLE T_CHILD ADD CONSTRAINT T_CHILD_FK_TO_T_PARENT  FOREIGN KEY ( COL_C) REFERENCES T_PARENT ( COL_P )
                                                                                                      *
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list


SQL>
SQL>
SQL>
SQL> CREATE INDEX T_NORMAL_COL_P_IDX1 ON T_PARENT ( COL_P,COL_P2);

Index created.

SQL>
SQL> ALTER TABLE T_PARENT ADD CONSTRAINT T_PARENT_COL_P_UQ UNIQUE ( COL_P) USING INDEX T_NORMAL_COL_P_IDX1;

Table altered.

SQL>
SQL>
SQL>
SQL> ALTER TABLE T_CHILD ADD CONSTRAINT T_CHILD_FK_TO_T_PARENT  FOREIGN KEY ( COL_C) REFERENCES T_PARENT ( COL_P ) ;

Table altered.

SQL>
SQL>

Re: ORA-02270 on FK creation attempt [message #686060 is a reply to message #686058] Mon, 06 June 2022 03:09 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
John Watson wrote on Sun, 05 June 2022 17:06
There's some history behind this. B-tree indexes were introduced in (IIRC) release 6, and you could define them as unique or nonunique. A unique index was the closest we had to a primary key constraint back then, because Oracle did not have constraints until release 7.

Possibly due to this historical anomaly, I believe that there is a stupid optimization failure that persists to this day. Well, up to 19.3, I haven't tested subsequently. If you define a unique constraint and select with an equality predicate, there is always one extra block read if the index is nonunique rather than unique. I think this is because with a nonunique index, after finding the leaf block that has the key Oracle then looks at the next leaf block to see if the key is repeated there. Why? The constraint should tell it that the key cannot be repeated. You don't get the extra block read if the index is unique. This is irritating because in other ways (deferrable constraints are the most obvious) nonunique indexes are better to work with.

I have long believed (I am very much open to correction on this) that there is no difference between the structure of a unique and a nonunique index: that it is only a dictionary definition thing.
Many thanks, always a pleasure to hear your input and conceptual point of view on things.


I tried to see if I can do a comparison testcase ( Please forgive, and feel free to correct me if I got stuff inaccurate ):

SQL>
SQL> drop table table_with_uq_ind purge;

Table dropped.

Elapsed: 00:00:00.54
SQL> drop table table_with_normal_ind purge;

Table dropped.

Elapsed: 00:00:00.03
SQL>
SQL>
SQL> alter session set optimizer_dynamic_sampling=0;

Session altered.

Elapsed: 00:00:00.00
SQL> set lines 900 pages 20000 autot off timing on
SQL>
SQL>
SQL>
SQL> create table table_with_uq_ind ( col1 number,col2 varchar2(100));

Table created.

Elapsed: 00:00:00.03
SQL>
SQL> create UNIQUE index uq_ind on table_with_uq_ind ( col1);

Index created.

Elapsed: 00:00:00.03
SQL>
SQL>
SQL> insert into table_with_uq_ind select object_id,object_name from user_objects where rownum < 11;

10 rows created.

Elapsed: 00:00:00.06
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL>
SQL>
SQL> create table table_with_normal_ind ( col1 number, col2 varchar2(100));

Table created.

Elapsed: 00:00:00.01
SQL>
SQL>
SQL> create index normal_ind on table_with_normal_ind ( col1);

Index created.

Elapsed: 00:00:00.00
SQL>
SQL> insert into table_with_normal_ind select object_id,object_name from user_objects where rownum < 11;

10 rows created.

Elapsed: 00:00:00.03
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL>
SQL> set autot on
SQL>
SQL>
SQL> EXEC rdsadmin.rdsadmin_util.flush_shared_pool;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> EXEC rdsadmin.rdsadmin_util.flush_buffer_cache;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.21
SQL> exec dbms_lock.sleep(1);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.08
SQL>
SQL>
SQL> select /*+ index ( table_with_uq_ind uq_ind ) */ col1 from table_with_uq_ind where col1 = 56726;

      COL1
----------
     56726

Elapsed: 00:00:00.15

Execution Plan
----------------------------------------------------------
Plan hash value: 1312559427

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| UQ_IND |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COL1"=56726)


Statistics
----------------------------------------------------------
        128  recursive calls
          0  db block gets
        168  consistent gets
         23  physical reads
          0  redo size
        356  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         23  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>
SQL> EXEC rdsadmin.rdsadmin_util.flush_shared_pool;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> EXEC rdsadmin.rdsadmin_util.flush_buffer_cache;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17
SQL> exec dbms_lock.sleep(1);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.05
SQL>
SQL>
SQL>
SQL> select /*+ index ( table_with_normal_ind normal_ind ) */ col1 from table_with_normal_ind where col1 = 56726;

      COL1
----------
     56726

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 3662600481

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     4 |    52 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| NORMAL_IND |     4 |    52 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COL1"=56726)


Statistics
----------------------------------------------------------
        132  recursive calls
          0  db block gets
        168  consistent gets
         23  physical reads
          0  redo size
        356  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         23  sorts (memory)
I repeated this 3 times ( I know what they say about a person who repeats the same process and expects a different outcome Smile .. But I just wanted to make sure that the metrics align the same to prove that the process replication is truthful.. )

Seems like only the recursive calls are a bit higher on the normal index scan ( 132 vs 128 ).

Is this what you mean ?


Btw I tried to perform a full index scan but seems like I messed something up in the process as it does a full scan Sad

SQL> select /*+ index ( table_with_normal_ind normal_ind ) */ col1 from table_with_normal_ind;

      COL1
----------
     56726
     55228
     55018
     56393
     56163
     55189
     55215
     57797
     57798
     55577

10 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3394346367

-------------------------------------------------------------------------------------------
| Id  | Operation         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                       |    10 |   130 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TABLE_WITH_NORMAL_IND |    10 |   130 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   1 -  SEL$1 / TABLE_WITH_NORMAL_IND@SEL$1
         U -  index ( table_with_normal_ind normal_ind )


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          6  physical reads
          0  redo size
        464  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

Re: ORA-02270 on FK creation attempt [message #686061 is a reply to message #686060] Mon, 06 June 2022 04:16 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Seems like only the recursive calls are a bit higher on the normal index scan ( 132 vs 128 ).
The recursive calls, and most of the block reads, are because of parsing: you have to run the statement at least twice, without flushing the shared pool, to get the IO for the statement itself:
orclz>
orclz> set autot trace stat
orclz> create unique index ni on emp(ename);

Index created.

orclz> alter table emp add constraint nuk unique(ename);

Table altered.

orclz> select * from emp where ename='KING';


Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
       1101  bytes sent via SQL*Net to client
        398  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

orclz> select * from emp where ename='KING';


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1101  bytes sent via SQL*Net to client
        398  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

orclz> alter table emp drop constraint nuk;

Table altered.

orclz> drop index ni;

Index dropped.

orclz> create index ni on emp(ename);

Index created.

orclz> alter table emp add constraint nuk unique(ename);

Table altered.

orclz> select * from emp where ename='KING';


Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
         25  consistent gets
          0  physical reads
          0  redo size
       1101  bytes sent via SQL*Net to client
        398  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

orclz> select * from emp where ename='KING';


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1101  bytes sent via SQL*Net to client
        398  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

orclz>
It seems odd to me, but it is absolutely repeatable.

I think the reason you are getting full scans is because your indexed column is nullable. That can be an important optimization: if you know that a column has no nulls, you must declare it as such.
Re: ORA-02270 on FK creation attempt [message #686062 is a reply to message #686061] Mon, 06 June 2022 04:47 Go to previous message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
John Watson wrote on Mon, 06 June 2022 12:16
Quote:
Seems like only the recursive calls are a bit higher on the normal index scan ( 132 vs 128 ).
The recursive calls, and most of the block reads, are because of parsing: you have to run the statement at least twice, without flushing the shared pool, to get the IO for the statement itself:
I actually have flushed both shared pool and buffer cache before running any of my two statements ( that's why I always get physical reads 23, right ? )

SQL> EXEC rdsadmin.rdsadmin_util.flush_shared_pool;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> EXEC rdsadmin.rdsadmin_util.flush_buffer_cache;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.21
SQL> exec dbms_lock.sleep(1);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.08
SQL>
SQL>
SQL> select /*+ index ( table_with_uq_ind uq_ind ) */ col1 from table_with_uq_ind where col1 = 56726;
..
..
..

[quote title=John Watson wrote on Mon, 06 June 2022 12:16]Quote:

I think the reason you are getting full scans is because your indexed column is nullable. That can be an important optimization: if you know that a column has no nulls, you must declare it as such.

Well, I *think* that if Oracle *can* use the index for the query and I present a hint to force it to - it should do so.

And it seems correct. I've set it to not null in my example, and now it worked ( parsed with full index scan ).

So I understand that it means that it really couldn't, as nulls were not indexed, right ?



And if I follow with the comparison ( flushing buffer cache and shared pool before any of the queries ) I get quit less recursive calls, consistent reads and even physical reads:

SQL> SET AUTOT OFF
SQL> alter table TABLE_WITH_UQ_IND modify col1 not null;

Table altered.

Elapsed: 00:00:00.04
SQL>
SQL> alter table TABLE_WITH_NORMAL_IND modify col1 not null;

Table altered.

Elapsed: 00:00:00.01
SQL>
SQL>
SQL> SET AUTOT OFF
SQL> EXEC rdsadmin.rdsadmin_util.flush_shared_pool;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
SQL> EXEC rdsadmin.rdsadmin_util.flush_buffer_cache;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
SQL> exec dbms_lock.sleep(1);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.11
SQL>
SQL> SET AUTOT ON
SQL>
SQL>
SQL>
SQL> select /*+ index ( table_with_normal_ind normal_ind ) */ col1 from table_with_normal_ind;

      COL1
----------
     55018
     55189
     55215
     55228
     55577
     56163
     56393
     56726
     57797
     57798

10 rows selected.

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
Plan hash value: 2033086612

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |    10 |   130 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | NORMAL_IND |    10 |   130 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        366  recursive calls
          0  db block gets
        456  consistent gets
         40  physical reads
          0  redo size
        464  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         52  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL>
SQL>
SQL>
SQL>
SQL> SET AUTOT OFF
SQL> EXEC rdsadmin.rdsadmin_util.flush_shared_pool;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> EXEC rdsadmin.rdsadmin_util.flush_buffer_cache;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.23
SQL> exec dbms_lock.sleep(1);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.04
SQL>
SQL> SET AUTOT ON
SQL>
SQL>
SQL> select /*+ index ( table_with_uq_ind uq_ind ) */ col1 from table_with_uq_ind;

      COL1
----------
     55018
     55189
     55215
     55228
     55577
     56163
     56393
     56726
     57797
     57798

10 rows selected.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 4043856755

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    10 |   130 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | UQ_IND |    10 |   130 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        124  recursive calls
          0  db block gets
        176  consistent gets
         23  physical reads
          0  redo size
        464  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         23  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL>
SQL>
SQL>



Doesn't it serve as an indication that either the structure is actually different, or the process ( the way it does the FULL INDEX SCAN on the NORMAL vs. UNIQUE index ) ?

[Updated on: Mon, 06 June 2022 04:50]

Report message to a moderator

Previous Topic: Trigger created with compilation errors (invalid specification trigger)
Next Topic: file transfer mechanism (FTP/SFTP/SSHFTP) using purely PL/SQL
Goto Forum:
  


Current Time: Thu Mar 28 04:38:33 CDT 2024