Forum OpenACS Q&A: Oracle: dropping check constraints

Posted by Jade Rubick on 12/19/01 10:51 PM

I've created a table, defined as follows:

create table formula (
	type                    char(1) default 'd'
				check (type in ('d','o','w')),
)

I wanted to change the check constraint to also have the the option for 't'.

It seems you can't do this by simply modifying the table:

SQL> alter table formula modify (
  2  type char(1) default 'd' check (type in ('d','o','t','w')));
type char(1) default 'd' check (type in ('d','o','t','w')))
                         *
ERROR at line 2:
ORA-02253: constraint specification not allowed here

So I delved into my hefty Oracle8: The Complete Reference, and it seems there is a nifty table that keeps track of all the constraints:

SQL> select constraint_name,search_condition from user_constraints
where table_name = 'FORMULA' and constraint_name = 'SYS_C005027';

CONSTRAINT_NAME     SEARCH_CONDITION
---------------     ----------------
SYS_C005027         type in ('d','o','w')

So I know the Constraint name. I thought I could then simply update it, but it doesn't seem to work:

SQL> update user_constraints set search_condition = 'type in
''d'',''o'',''t'',''w'')' where constraint_name = 'SYS_C005027';
update user_constraints set search_condition = 'type in
''d'',''o'',''t'',''w'')' where constraint_name = 'SYS_C005027'
       *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> connect internal
Connected.
SQL> update user_constraints set search_condition = 'type in
''d'',''o'',''t'',''w'')' where constraint_name = 'SYS_C005027';
update user_constraints set search_condition = 'type in
''d'',''o'',''t'',''w'')' where constraint_name = 'SYS_C005027'
                            *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved
table

Deletion doesn't work either. Any ideas? The double quote thing is correct, isn't it?

As always, I'm very appreciative of assistance! 😊

2: Response to Oracle: dropping check constraints (response to 1)

Posted by Daryl Biberdorf on 12/20/01 12:09 AM

user_constraints is a view that allows you to view parts of the data dictionary that your schema owns. It's a no-no to modify the data dictionary with anything other than DDL (Data Definition Language) statements: CREATE, ALTER, DROP.

To do what you want, you'll need to drop the constraint and re-create it:

  SQL> create table dlb_test (id number not null primary key, xp char(1));

  Table created.

  SQL> alter table dlb_test add constraint dlb_const check (xp IN ('Y','N'));

  Table altered.

  SQL> alter table dlb_test drop constraint dlb_const;

  Table altered.

  SQL> alter table dlb_test add constraint dlb_const check (xp IN ('Y','N','M'));

  Table altered.

  SQL>

3: Response to Oracle: dropping check constraints (response to 1)

Posted by Jade Rubick on 12/20/01 12:27 AM

Thanks, Daryl.

I guess I just learned the importance of naming your constraints. Using the alter statement with the SYS_C005027 as the constraint name worked.

4: Response to Oracle: dropping check constraints (response to 1)

Posted by Daryl Biberdorf on 12/20/01 04:26 AM

Hey, as the "Migration master" around our shop, it's amazing how cumbersome default constraint names can be. Even for primary keys. Glad I could help you out in your predicament. As an unsolicited recommendation, I highly suggest Tom Kyte's new book. Awesome stuff!