Re: problem modify column constraint

What you can do is add a constraint outside of the create table definition. I'm not sure that you can modify constraints created with create table. I don't know of any straight forward way to do it.

SQL> create table phone_numbers (

  2  email           varchar(100) not null references mailing_list,
  3  number_type     varchar(15),
  4  phone_number    varchar(20));

SQL> alter table phone_numbers add constraint number_types   2 check (number_type in ('work','home'));

Table altered.

SQL> alter table phone_numbers drop constraint number_types;

Table altered.

SQL> alter table phone_numbers add constraint number_types   2 check (number_type in ('work','home','cell');

Table altered.

Copied from Oracle 7.3.4 documentation:

The only type of integrity constraint that you can add to an existing column using the MODIFY clause with the column constraint syntax is a NOT
NULL constraint. However, you can define other types of integrity constraints (UNIQUE, PRIMARY KEY, referential integrity, and CHECK constraints) on existing columns using the ADD clause and the table constraint syntax.

Hope this helps,
Erik

Richard Chen wrote:
>
> The docs says one can modify column constraint using alter.
> But the following does not work:
>
> SQL> drop table phone_numbers;
>
> Table dropped.
>
> SQL> create table phone_numbers (
> 2 email varchar(100) not null references mailing_list,
> 3 number_type varchar(15) check (number_type in ('work','home')),
>
> 4 phone_number varchar(20));
>
> Table created.
>
> SQL> alter table phone_numbers modify (number_type varchar(15) check
> (number_type in ('work','home','cell')));
>
> alter table phone_numbers modify (number_type varchar(15) check
> (number_type in ('work','home','cell')))
> *
> ERROR at line 1:
> ORA-02253: constraint specification not allowed here
>
> Is there a syntax error or is it not possible to modify constraints like
> this?
>
> Thanks for any info.
>
> Richard
Received on Tue Jun 08 1999 - 10:41:45 CDT