CODEWITHSUNDEEP
sqlconstraintssqlanywhere
Taylor Williams
Taylor Williams

Reputation: 356

dropping unique constraint by name

When dropping a unique constraint in Sybase Central, the drop statement shown is as follows;

ALTER TABLE "DBA"."enr_rds_heating_control_validation_t" DROP CONSTRAINT "ASA826";

The constraint being dropped here ASA826 comes from the SYS.SYSCONSTRAINT view where the value comes from the constraint_name column.

The issue here is that this is a unique identifier assigned to the constraint by Sybase and is only valid for the current database, so when run against another database this constraint might a) not exist or b) be a different constraint entirely.

When creating the constraint we are naming it however, in this case enr_rds_heating_control_validation_t UNIQUE (enr_rds_heating_type_id,enr_rds_heating_control_id) and this value can be seen in the SYS.SYSINDEX view in the index_name column and this value will remain unique across all of the databases.

I have tried to drop the constraint passing through the index_name in place of the constraint name and receive an error indicating that the constraint is not found.

How do I drop the unique constraint using the index name rather than the constraint name?

Upvotes: 1

Views: 1923

Answers (1)

abe_crabbers
abe_crabbers

Reputation: 201

I don't think you can drop a constraint just as if it were an index, even though physically Sybase uses an index and you see it on the table as an index as reported by sp_help.

DROP INDEX is only for an index created with CREATE INDEX. Used on a constraint you get:

Cannot use DROP with 'abe.c' because 'c' is a constraint. Use ALTER TABLE.

It sounds like you want to take constraints created by Sybase Central as SQL and then use the sql in bulk, but not on the database the Sql relates too - isn't the answer that you just can't do that?

As long as you make your scripts that are using this Sql Central output "use" the correct database, you'll be alright.

I can't understand exactly what you're doing, but it does seem like you'll have to stick with what SQL Central is generating.

Or else post-process Sql Central Sql and suffix all constraint names with db_id()? That's a wild guess though

Upvotes: 1

Related Questions