Constraints (Part 5) - ORA-02270, ORA-02249, ORA-02291 and ORA-02292

Go to part 4

This example, tested on an Oracle 9 database, starts to look at foreign key constraints. First create a department table. Each row has a department number and description:

SQL> create table dept
  2  (dept_no   varchar2(4),
  3   dept_desc varchar2(10))
  4  /

Table created.

SQL> 

Next, create an employee table. Each row has an employee number, name and department number. A foreign key constraint checks that the department number is already present in the department table:

SQL> create table emp
  2  (emp_no      varchar2(4),
  3  emp_name     varchar2(10),
  4  emp_dept_no  varchar2(4)
  5  constraint fk_dept_no
  6  references dept(dept_no))
  7  /
references dept(dept_no))
                *
ERROR at line 6:
ORA-02270: no matching unique or primary key for this
column-list

SQL>

That fails because the department number in the department table does not have to be unique. Add a constraint to ensure it is then try again:

SQL> alter table dept add constraint dept_no_pk
  2  unique(dept_no)
  3  /

Table altered.

SQL> create table emp
  2  (emp_no      varchar2(4),
  3  emp_name     varchar2(10),
  4  emp_dept_no  varchar2(4),
  5  constraint fk_dept_no
  6  foreign key (emp_dept_no)
  7  references dept(dept_no))
  8  /

Table created.

SQL>

So far so good. Now see what happens if you try to drop the department table. Note that both tables are empty at this point:

SQL> drop table dept
  2  /
drop table dept
          *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by
foreign keys

SQL> 

That fails too because the department table is now required to validate changes to the employee table. Try to add a row to the employee table:

SQL> insert into emp values ('E001','Andrew','D001')
  2  /
insert into emp values ('E001','Andrew','D001')
*
ERROR at line 1:
ORA-02291: integrity constraint (ORACLE.FK_DEPT_NO)
violated - parent key not found

SQL>

The insert is rejected because the department number has not been added to the department table yet. Add a couple of entries in the department table (including the one needed for the employee row above) and have another go:  

SQL> insert into dept values ('D001','IT')
  2  /

1 row created.

SQL> insert into dept values ('D002','Sales')
  2  /

1 row created.

SQL> insert into emp values ('E001','Andrew','D001')
  2  /

1 row created.

SQL>

That worked. Now try to delete rows from the department table:

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT
D002 Sales

SQL> delete dept
  2  /
delete dept
*
ERROR at line 1:
ORA-02292: integrity constraint (ORACLE.FK_DEPT_NO)
violated - child record found

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT
D002 Sales

SQL>

That didn't work either because D001 already has one employee in the emp table. Note that D002 was not deleted either, even though it has no employees. Finally, drop both tables. If you do the emp table first, there is no problem:

SQL> drop table emp
  2  /

Table dropped.

SQL> drop table dept
  2  /

Table dropped.

SQL>

Go to part 6

Constraints (Part 4) - ORA-02299

Go to part 3

This was tested on an Oracle 11.2 database. As I have already said, you can add a constraint when a table is created or you can modify an existing table as shown below: 

SQL> create table tab1 as

  2  select trunc(sysdate) today from dual

  3  /

 

Table created.

 

SQL> alter table tab1 modify today unique

  2  /

 

Table altered.

 

SQL> select constraint_name from user_constraints

  2  where table_name = 'TAB1'

  3  /

 

CONSTRAINT_NAME

------------------------------

SYS_C00153494

 

SQL>

You can use a system generated name for the constraint as shown above or you can choose a name yourself as shown below:

SQL> create table tab2 as

  2  select trunc(sysdate) today from dual

  3  /

 

Table created.

 

SQL> alter table tab2

  2  modify today constraint con2 unique

  3  /

 

Table altered.

 

SQL> select constraint_name from user_constraints

  2  where table_name = 'TAB2'

  3  /

 

CONSTRAINT_NAME

------------------------------

CON2

 

SQL>

If you try to add a unique constraint to a column with duplicate values, you get an ORA-02299:

SQL> create table tab3 as

  2  select trunc(sysdate) today from dual

  3  /

 

Table created.

 

SQL> insert into tab3 select * from tab3

  2  /

 

1 row created.

 

SQL> alter table tab3

  2  modify today constraint con3 unique

  3  /

modify today constraint con3 unique

                        *

ERROR at line 2:

ORA-02299: cannot validate (ORACLE.CON3) - duplicate

keys found

 

SQL>

To get round this, you have to delete the duplicate values:

SQL> delete from tab3 a

  2  where rowid >

  3  (select min(rowid) from tab3

  4   where today = a.today)

  5  /

 

1 row deleted.

 

SQL>

Then you can add the constraint without Oracle giving you an error:

SQL> alter table tab3

  2  modify today constraint con3 unique

  3  /

 

Table altered.

 

SQL>

Go to part 5

Constraints (Part 3) - More About Unique Constraints

Go to part 2

This example, tested on an Oracle 11 database, shows a different way to set up a unique constraint. As before, a table is created:

SQL> create table my_table

  2  (my_column number)

  3  /

Table created.

SQL>

Then a check is carried out to ensure there is no constraint or unique index called MY_UK:

SQL> select owner, constraint_name, constraint_type, status

  2  from dba_constraints

  3  where constraint_name = 'MY_UK'

  4  /

no rows selected

SQL> select owner, index_name, uniqueness

  2  from dba_indexes

  3  where index_name = 'MY_UK'

  4  /

no rows selected

SQL>

A constraint is added to the table:

SQL> alter table my_table

  2  add constraint my_uk

  3  unique (my_column)

  4  /

Table altered.

SQL>

This time the new constraint is included in DBA_CONSTRAINTS. Note the CONSTRAINT_TYPE of U, as this is a unique key constraint:

SQL> select owner, constraint_name, constraint_type, status

  2  from dba_constraints

  3  where constraint_name = 'MY_UK'

  4  /

OWNER      CONSTRAINT_NAME CONSTRAINT_TYPE STATUS

---------- --------------- --------------- --------

ANDREW     MY_UK           U               ENABLED

SQL>

There is also an index associated with the constraint:

SQL> select owner, index_name, uniqueness

  2  from dba_indexes

  3  where index_name = 'MY_UK'

  4  /

OWNER      INDEX_NAME UNIQUENESS

---------- ---------- ----------

ANDREW     MY_UK      UNIQUE

SQL>

A row is added to the table:

SQL> insert into my_table values (1)

  2  /

1 row created.

SQL>

And the unique constraint can then be checked by attempting to add the same row again:

SQL> insert into my_table values (1)

  2  /

insert into my_table values (1)

*

ERROR at line 1:

ORA-00001: unique constraint (ANDREW.MY_UK) violated

SQL> select * from my_table

  2  /

 MY_COLUMN

----------

         1

The constraint is disabled:

SQL> alter table my_table

  2  disable constraint my_uk

  3  /

Table altered.

SQL>

This changes its status:

SQL> select owner, constraint_name, constraint_type, status

  2  from dba_constraints

  3  where constraint_name = 'MY_UK'

  4  /

OWNER      CONSTRAINT_NAME CONSTRAINT_TYPE STATUS

---------- --------------- --------------- --------

ANDREW     MY_UK           U               DISABLED

SQL>

And the index disappears:

SQL> select owner, index_name, uniqueness

  2  from dba_indexes

  3  where index_name = 'MY_UK'

  4  /

no rows selected

SQL>

It is then possible to add duplicate data to the table:

SQL> insert into my_table values (1)

  2  /

1 row created.

SQL> select * from my_table

  2  /

 MY_COLUMN

----------

         1

         1

SQL>

Go to part 4

Constraints (Part 2) - ORA-00001 and ORA-02431

Go to part 1

The example below was checked on Oracle 9, 10 and 11. First a table was created:

SQL> create table my_table

  2  (my_column number)

  3  /

Table created.

SQL>

The number of constraints in the database was checked:

SQL> select count(*) from dba_constraints

  2  /

  COUNT(*)

----------

      7658

SQL>

A unique index was added to ensure that no value appeared more than once in my_column:

SQL> create unique index my_uk

  2  on my_table (my_column)

  3  /

Index created.

SQL>

The number of constraints was checked again but it had not changed. This suggested that creating the unique index had not added an extra constraint to the database:

SQL> select count(*) from dba_constraints

  2  /

  COUNT(*)

----------

      7658

SQL>

An attempt was made to insert duplicate values into my_column:

SQL> insert into my_table values (1)

  2  /

1 row created.

SQL>

The attempt failed but the error message displayed suggested that creating the unique index had, in fact, added a constraint:

SQL> insert into my_table values (1)

  2  /

insert into my_table values (1)

*

ERROR at line 1:

ORA-00001: unique constraint (ANDREW.MY_UK) violated

SQL>

The table was queried to check that the unique index had worked as expected:

SQL> select * from my_table

  2  /

 MY_COLUMN

----------

         1

SQL>

If this happens and you decide that you genuinely need to allow duplicates in my_column, how would you do this? You cannot disable the constraint because it does not exist:

SQL> alter table my_table

  2  disable constraint my_uk

  3  /

alter table my_table

*

ERROR at line 1:

ORA-02431: cannot disable constraint (MY_UK) - no

such constraint

SQL>

Although this constraint does not appear in DBA_CONSTRAINTS, you can see what is enforcing it by looking for an index with that name in DBA_INDEXES. It will have UNIQUENESS set to UNIQUE:

SQL> select uniqueness from dba_indexes

  2  where index_name = 'MY_UK'

  3  /

UNIQUENES

---------

UNIQUE

SQL>

So, to allow duplicate values in my_column, you just need to drop the unique index (and replace it with an ordinary one if you find that an index is still required).

Go to part 3

Constraints (Part 1) - ORA-01400 and ORA-02290

These examples were tested on an Oracle 9 database.

Constraints are used to validate table data. You can add a constraint when you create a table:

SQL> CREATE TABLE MY_TABLE

  2  (MY_COLUMN NUMBER NOT NULL)

  3  /

Table created.

SQL>

Or you can add one to an existing table:

SQL> ALTER TABLE MY_TABLE

  2  ADD CONSTRAINT MY_CONSTRAINT

  3  CHECK (MY_COLUMN < 5)

  4  /

Table altered.

SQL>

Constraints can check INSERT statements:

 SQL> INSERT INTO MY_TABLE VALUES(1)

  2  /

1 row created.

SQL> INSERT INTO MY_TABLE VALUES(5)

  2  /

INSERT INTO MY_TABLE VALUES(5)

*

ERROR at line 1:

ORA-02290: check constraint

(ORACLE.MY_CONSTRAINT) violated

SQL> INSERT INTO MY_TABLE VALUES(2)

  2  /

1 row created.

SQL> INSERT INTO MY_TABLE VALUES(NULL)

  2  /

INSERT INTO MY_TABLE VALUES(NULL)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into

("ORACLE"."MY_TABLE"."MY_COLUMN")

SQL>

And they can also check updates:

SQL> UPDATE MY_TABLE

  2  SET MY_COLUMN = 6

  3  WHERE MY_COLUMN = 2

  4  /

UPDATE MY_TABLE

*

ERROR at line 1:

ORA-02290: check constraint

(ORACLE.MY_CONSTRAINT) violated

SQL>

When you have finished, only the valid data remains in the table:

SQL> SELECT * FROM MY_TABLE

  2  /

 MY_COLUMN

----------

         1

         2

SQL>

Go to part 2

ORA-00960

This was tested on Oracle 11. I tried to sort on a column which appeared more than once in the SELECT list, and got an ORA-00960:

 

SQL> select username, username from dba_users

  2  order by username

  3  /

order by username

         *

ERROR at line 2:

ORA-00960: ambiguous column naming in select list

 

SQL>

 

In this situation, you need to change one or both of the names so that they no longer match:

SQL> l

  1  select username, user_id from dba_users

  2* order by username

SQL> /

 

 

USERNAME                          USER_ID

------------------------------ ----------

ANDREW                               8391

BRIAN                                8494

COLIN                                8403

Etc

 

In the next example, I wanted a list of users ordered by their creation date. Then for each user, I wanted to show the objects it owned along with their creation dates:

 

SQL> l

  1  select username, dba_users.created,

  2  object_name, dba_objects.created

  3  from dba_users, dba_objects

  4  where username = owner

  5* order by created

SQL> /

order by created

         *

ERROR at line 5:

ORA-00960: ambiguous column naming in select list

 

SQL>

 

In this case, there was a CREATED column in DBA_USERS and DBA_OBJECTS so I had to tell Oracle which one to sort on. There are two ways to do this. You can prefix the sort key with the table_name:

 

SQL> l

  1  select username, dba_users.created,

  2  object_name, dba_objects.created

  3  from dba_users, dba_objects

  4  where username = owner

  5* order by dba_users.created

SQL> /

 

USERNAME   CREATED   OBJECT_NAME CREATED

---------- --------- ----------- ---------

SYS        11-SEP-10 CON$        11-SEP-10

SYS        11-SEP-10 I_COL2      11-SEP-10

SYS        11-SEP-10 I_USER#     11-SEP-10

SYS        11-SEP-10 C_TS#       11-SEP-10

SYS        11-SEP-10 I_OBJ#      11-SEP-10

Etc

 

Alternatively, you can use an alias:

 

SQL> l

  1  select username, x.created,

  2  object_name, y.created

  3  from dba_users x, dba_objects y

  4  where username = owner

  5* order by x.created

SQL> /

 

USERNAME   CREATED   OBJECT_NAME CREATED

---------- --------- ----------- ---------

SYS        11-SEP-10 CON$        11-SEP-10

SYS        11-SEP-10 I_COL2      11-SEP-10

SYS        11-SEP-10 I_USER#     11-SEP-10

SYS        11-SEP-10 C_TS#       11-SEP-10

SYS        11-SEP-10 I_OBJ#      11-SEP-10

Etc

SPOOL...APPEND

According to the book I found this in, it was new in Oracle 10 but I tested it on Oracle 11.2.0.2.7. You can SPOOL output to a file like this…

 

SQL> spool spool_test

SQL> select 'Andrew' Andrew from dual

  2  /

 

ANDREW

------

Andrew

 

SQL> spool off

 

…then you can run an OS command from within SQL*Plus to display the contents of the SPOOL file:

 

SQL> !cat spool_test.lst

SQL> select 'Andrew' Andrew from dual

  2  /

 

ANDREW

------

Andrew

 

SQL> spool off

 

If you repeat the SPOOL command and run some more SQL…

 

SQL> spool spool_test

SQL> select 'Brian' Brian from dual

  2  /

 

BRIAN

-----

Brian

 

SQL> spool off

 

… the original contents of the output file will be overwritten:

 

SQL> !cat spool_test.lst

SQL> select 'Brian' Brian from dual

  2  /

 

BRIAN

-----

Brian

 

SQL> spool off

 

But if you add the word APPEND to the end of the SPOOL command and run some more SQL…

 

SQL> spool spool_test append

SQL> select 'Colin' Colin from dual

  2  /

 

COLIN

-----

Colin

 

SQL> spool off

 

…the new output is added to the end of the output file:

 

SQL> !cat spool_test.lst

SQL> select 'Brian' Brian from dual

  2  /

 

BRIAN

-----

Brian

 

SQL> spool off

SQL> select 'Colin' Colin from dual

  2  /

 

COLIN

-----

Colin

 

SQL> spool off