Adding check constraints

Gints Tervits

unread,

Jun 18, 1996, 3:00:00 AM6/18/96

to

Why Oracle produces following error:
SQL> ALTER TABLE my_table MODIFY my_column CHECK (my_column IN ('Y', 'N'));
ALTER TABLE my_table MODIFY my_column CHECK (my_column IN ('Y', 'N'))
*
ERROR at line 1:
ORA-02253: constraint specification not allowed here

Is it possible to add new check constraint to existing table?

Gints Tervits

Ivo Almanis

unread,

Jun 19, 1996, 3:00:00 AM6/19/96

to Gints Tervits

CHECK.LST

Peter Rak

unread,

Jun 19, 1996, 3:00:00 AM6/19/96

to Gints Tervits

Try:
alter table 'my_table' add constraint 'constraint_name'
check( 'my_column' in ('y','n'));

Peter.

Murray Kaiser

unread,

Jun 19, 1996, 3:00:00 AM6/19/96

to

Gints Tervits (ter...@acad.latnet.lv) wrote:
: Why Oracle produces following error:
: SQL> ALTER TABLE my_table MODIFY my_column CHECK (my_column IN ('Y', 'N'));
: ALTER TABLE my_table MODIFY my_column CHECK (my_column IN ('Y', 'N'))
: *
: ERROR at line 1:
: ORA-02253: constraint specification not allowed here

: Is it possible to add new check constraint to existing table?

Yes, but it is a table constraint not a column constraint as in:

1 ALTER TABLE MY_TABLE
2* ADD CONSTRAINT MY_CONSTRAINT CHECK (MY_COLUMN IN ('Y','N'))

Good luck
--
Murray Kaiser | Usual gutless disclaimer..
Nova Scotia Power | Opinions are mine and not necessarily
(Murray...@NSPower.NS.CA) | shared by my employer

Ed Bruce

unread,

Jun 19, 1996, 3:00:00 AM6/19/96

to Peter Rak

Peter Rak wrote:

>
> Gints Tervits wrote:
> >
> > Why Oracle produces following error:
> > SQL> ALTER TABLE my_table MODIFY my_column CHECK (my_column IN ('Y', 'N'));
> > ALTER TABLE my_table MODIFY my_column CHECK (my_column IN ('Y', 'N'))
> > *
> > ERROR at line 1:
> > ORA-02253: constraint specification not allowed here
> >
> > Is it possible to add new check constraint to existing table?
> >

> > Gints Tervits
>
> Try:
> alter table 'my_table' add constraint 'constraint_name'
> check( 'my_column' in ('y','n'));
>
> Peter.

The reason yours fails and Peter's works is that you can only add table
constraints with an alter ... add, not column constraints. You may ask
what's the difference. I don't know, because you can create a table
constraint that does the correct constraint on a column. I have not seen
any difference in the end results, just the semantics (or is that
syntax).

Ed Bruce
Systems Engineer
Hughes Aircraft Company
--
Bloody useless text so I can post this message and not get a bloody NNTP
error about having more included text then new. Just a bloody waste of
bandwidth. Someone suggested it was Netscape enforcing this, but the
error seems to come back from my NNTP server. Just bloody stupid, because
I waste my time typing this and waste bloody bandwidth. Let's see if this
does it.

Just to be safe
aaaaaaaaaaa
bbbbbbbbbbb
cccccccccc
ddddddddd

Vijay Banda

unread,

Jun 25, 1996, 3:00:00 AM6/25/96

to

);ter...@acad.latnet.lv (Gints Tervits) wrote:
>Why Oracle produces following error:
>SQL> ALTER TABLE my_table MODIFY my_column CHECK (my_column IN ('Y', 'N'));
>ALTER TABLE my_table MODIFY my_column CHECK (my_column IN ('Y', 'N'))
> *
>ERROR at line 1:
>ORA-02253: constraint specification not allowed here
>
>Is it possible to add new check constraint to existing table?
>
>Gints Tervits

Yes it is possible to add new Check constraint to an existing table
--Use ADD instead of Modify in the alter table command.

Try this ,

alter table item ADD CONSTRAINT QTY_CK check (qty > 0 );

ALTER TABLE TEST ADD
CONSTRAINT TEST_CK CHECK ( TEST_ONE IN ('Y', 'N'));

Later!!

Vijay Kumar Banda
Ford Motors!