how to modify an existing check constraint?

Question

Is there any way to modify an existing check constraint on a table other than dropping and re-creating it?

create table t ( n number);
ora10g> Tabelle wurde erstellt.

ora10g> alter table t add constraint ck check(n>0);

Tabelle wurde geõndert.

ora10g> alter table t modify constraint ck check(n<0);
alter table t modify constraint ck check(n<0)
                                   *
FEHLER in Zeile 1:
ORA-00933: SQL-Befehl wurde nicht korrekt beendet

Kaleena

i think that the problem is that you surround your expression, and after 'check' it has to be a space – Ible

Answer 1

You have to drop it and recreate it, but you don't have to incur the cost of revalidating the data if you don't want to.

alter table t drop constraint ck ;
alter table t add constraint ck check (n < 0) enable novalidate;

The enable novalidate clause will force inserts or updates to have the constraint enforced, but won't force a full table scan against the table to verify all rows comply.

Fatherhood

This is fine if there are no connections to the database. See this answer before doing the change in a general case. – Chon

DDL against a table requires an X lock against it, but a transaction could sneak in between the two. I guess a better approach would be to create new constraint, drop old constraint, rename new constraint to old constraint name. In the case posted, however, the two constraints were exclusive to each other. – Fatherhood

Answer 2

Create a new constraint first and then drop the old one.
That way you ensure that:

• constraints are always in place
• existing rows do not violate new constraints
• no illegal INSERT/UPDATEs are attempted after you drop a constraint and before a new one is applied.

Cloutier

This won't work if you want to preserve the name of the constraint. You need to drop the constraint first and then create a new one. – Uniaxial

you still can do a trick: 1/ create the new constraint with a temporary name. 2/ drop the original constraint, so its name will be available. 3/ create a second copy of the new constraint with its final name. 4/ drop the temporary constraint. – Arjuna

@Arjuna Why create a second copy of the new constraint with the final name? Why not skip step 3 and just rename the temporary constraint to the original? – Mystery

@Mystery you're absolutely right! you have this check constraint: alter table test add constraint chk_id check (0 < id); you want to change it: alter table test add constraint tmp_id check (1 <= id); alter table test drop constraint chk_id; alter table test rename constraint tmp_id to chk_id; – Arjuna

Answer 3

NO, you can't do it other way than so.

Testes

Answer 4

No. If such a feature existed it would be listed in this syntax illustration. (Although it's possible there is an undocumented SQL feature, or maybe there is some package that I'm not aware of.)

Scruff

This is exactly the problem. This feature is listed in the documentation . But it does not work. – Kaleena

Modifying a constraint is documented, but the specific type of change you're looking for is not documented. Only changing the constraint state is allowed. e.g. enable, disable, validate, etc. – Scruff

Answer 5

create table Gender_Long(id integer,gender varchar2(10), constraint Gender_Long_pk primary key(id));
create table Employee(id integer,name varchar2(10),gender integer, constraint Employee_pk primary key(id),
    constraint Employee_fk foreign key(gender) references Gender_Long(id));

insert into Gender_Long values(1,'male');
insert into Gender_Long values(2,'female');

insert into Employee values(1,'Raj',1);
insert into Employee values(2,'Maha',1);
insert into Employee values(3,'Devi',2);

create table Gender_Short(id integer,gender varchar2(10), constraint Gender_Short_pk primary key(id));

insert into Gender_Short values(1,'M');
insert into Gender_Short values(2,'F');

ALTER TABLE Employee add constraint Employee_fk2 foreign key(gender) references Gender_Short(id);

ALTER TABLE Employee DROP CONSTRAINT Employee_fk;

drop table Gender_Long;

Minard