Re: Naming DEFAULT constraints

Paul schrieb:
> Hi,
>
> I am attempting to rename all of the DEFAULT constraints in a database
> so that they have controlled names (i.e. not system generated one).
>
> My plan was to find the system generated name, drop the constraint then
> recreate it.
>
> I cannot seem to find a way of creating a named default constraint on a
> colum.
>
> I have tried:
>
> ALTER TABLE <TABLE NAME>
> ADD CONSTRAINT DF1_<TABLE NAME> DEFAULT <VALUE> (<COLUMN>);
>
> This fails
>

There are no such integrity constraints in oracle as default (however i heard , somebody sometimes consider it as constraint).

SQL> create table abc(id number, text varchar2(400) default 'Hello');

Table created.

SQL> select * from user_constraints where table_name = 'ABC';

no rows selected

> I have also tried:
>
> ALTER TABLE <TABLE_NAME>
> MODIFY
> (<COLUMN_NAME> DEFAULT DF1<TABLE_NAME> <VALUE>;
>
> Neither work and I am unsure why.

Me too, maybe, because you leaved one closing bracket out? In either case, you don't provide the exact statement, neither error message. As for me - works perfectly:

SQL> alter table abc modify text default 'World';

Table altered.

SQL> alter table abc modify (text default 'Again Hello');

Table altered.

>
> Does anybody know if this is possible and if so how I can acheive it.
> It is not good enough just to be able to rename the existing constraint
> as I need to be able to add new named ones in the future.
>
> If anybody can suggest anything it would be greatly appreciated.
>
> Thanks in advance,
>
> Paul
>

Best regards

Maxim Received on Fri Jan 26 2007 - 11:30:02 CST