Originally posted by: puffpio
Hi
I know you are able to update constraints such as foreign keys and such.
I have a table w/ checks in is such as seen below
how can I update that check constraint w/o blowing away the table and recreating it? Since the constraint is not named, how do I locate it and change it?
Use the user_constraints view:
select constraint_name,search_condition from user_constraints
where table_name = 'FOOBARTABLE';
Find the constraint name that has your range check. For instance the query might return this:
SQL> select constraint_name, search_condition from user_constraints where table_name='FOOBARTABLE';
CONSTRAINT_NAME SEARCH_CONDITION
------------------------------ ------------------------------
SYS_C008663 "IDX" IS NOT NULL
SYS_C008664 "RANGE" IS NOT NULL
SYS_C008665 range between 0 and 100
So in this case SYS_C008665 is the constraint you want to alter. So now just simply drop it and add a new one:
alter table foobartable drop constraint SYS_C008665;
alter table foobartable add constraint check_range check(range between 0 and 100);
It's much more manageable to create all your constraints in separate alter table queries rather than within the create table. Relying on Oracle assigning you a constraint name like SYS_C008665 is not a good practice
🙂
