Skip to Content
0

dropping constraint ASE 16

May 17, 2017 at 05:17 PM

86

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Bret Halford
May 17, 2017 at 09:05 PM
1

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
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks a lot Bret

Jose

0