Skip to Content

dropping constraint ASE 16

Hello

Assume the following script in SAP ASE 16:

create table T1 ( c1 int , c2 char(1) constraint [c_2.id] unique(c2) )

go

This will create the table and when running

sp_helpconstraint T1

go

It'll show:

name definition

c_2.id UNIQUE INDEX (c2) :NON CLUSTERED

Then , when trying to drop it:

alter table T1 drop constraint c_2.id

go

Msg 102,"Incorrect syntax near "."

If you use

alter table T1 drop constraint [c_2.id]

go

Msg 3701 , "object does not exist in system catalog".

Any known issue?

Thank you

Regards

Jose

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    May 17, 2017 at 09:05 PM

    Yes, there has been an open CR on this for a few years, CR 745608. We don't yet have a KBA written for it.

    As a workaround, you can manually rename the index that was created for the constraint, force ASE to refresh the memory descriptors to pick up the new name, and then drop the constraint using the new name. Something like this, substitute appropriate values for <index name>, <dbname>, and <tablename>:

    sp_configure "allow updates", 1
    go
    update sysindexes set name = "drop_me" where name = "<index name>"
    go

    set switch on 3604
    go

    -- free the descriptors for the table so that index descriptor will be reinitialized reflecting our change

    dbcc cacheremove(<dbname>, <tablename>)
    go
    alter table <tablename> drop constraint "drop_me"
    go

    -bret
    Add comment
    10|10000 characters needed characters exceeded