Re: Check Constraint

I think what you will have to do is figure out what constraint was created, drop that constraint and then recreate it. It can be done indepently from dropping the table because the constraint clauses that are used with CREATE TABLE are merely there for convenience.

If you do not provide a name for a constraint, Oracle will generate one with the convention of SYS_COnnnnn where nnnnn is a 5-digit number. These can be found in SYS.DBA_CONSTRAINTS unless it is an index and then it is in SYS.DBA_INDEXES. In your case it is not an index. If you SELECT from DBA_CONSTRAINTS WHERE OWNER = XXXX AND TABLE_NAME = XXXXX you'll get a list of the constraints. Figure out which one is applicable and then drop it.

When you recreate the constraint I'd suggest that you give it a name - that way it will be easier to identify the next time you need to make a change.

Ken

wolfgang karrer wrote:

> How can i change a check constraint of a table
> :
> example:
> CREATE TABLE MEDIA
> (mediaNr INTEGER PRIMARY KEY,
> object BLOB NOT NULL,
> playtime INTEGER NOT NULL,
> mediatype VARCHAR2(10),
> constraint media_type_check
> check (mediatype in ('Audio', 'Video', 'Picture')));
>
> in
> CREATE TABLE MEDIA
> (mediaNr INTEGER PRIMARY KEY,
> object BLOB NOT NULL,
> playtime INTEGER NOT NULL,
> mediatype VARCHAR2(10),
> constraint media_type_check
> check (mediatype in ('Audio', 'Video', 'Picture',
> 'Macromedia','Executables')));
>
> Is there an ALTER TABLE MODIFY CONSTRAINT available??
>
> Thanks
Received on Tue Aug 17 1999 - 06:52:40 CDT