Menu
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 
665
Using an ALTER TABLE statement. The syntax for creating a check constraint in an ALTER TABLE statement in Oracle is: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]; The DISABLE keyword is optional.
The basic syntax of an ALTER TABLE command to ADD PRIMARY KEY constraint to a table is as follows. ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...); The basic syntax of an ALTER TABLE command to DROP CONSTRAINT from a table is as follows.
An existing constraint cannot be modified. To define another column, or set of columns, as the primary key, the existing primary key definition must first be dropped, and then re-created.
No. We cannot alter the constraint, only thing we can do is drop and recreate it.
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.
160
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
