CODEWITHSUNDEEP
sqldb2constraints
Chamila Wijayarathna
Chamila Wijayarathna

Reputation: 1933

Drop DB2 constraint using SQL

I have a DB2 database called 'test' and I have created a table there using following query.

CREATE TABLE IDP_PROVISIONING_ENTITY (
        ID INTEGER NOT NULL,
        PROVISIONING_CONFIG_ID INTEGER NOT NULL,
        ENTITY_TYPE VARCHAR(255) NOT NULL,
        ENTITY_LOCAL_USERSTORE VARCHAR(255) NOT NULL,
        ENTITY_NAME VARCHAR(255) NOT NULL,
        ENTITY_VALUE VARCHAR(255) NOT NULL,
        TENANT_ID INTEGER NOT NULL,
        PRIMARY KEY (ID),
        UNIQUE (ENTITY_TYPE, TENANT_ID, ENTITY_LOCAL_USERSTORE, ENTITY_NAME),
        UNIQUE (PROVISIONING_CONFIG_ID, ENTITY_TYPE, ENTITY_VALUE),
        FOREIGN KEY (PROVISIONING_CONFIG_ID) REFERENCES IDP_PROVISIONING_CONFIG(ID) ON DELETE CASCADE)
/

I want to drop the constraint "UNIQUE (ENTITY_TYPE, TENANT_ID, ENTITY_LOCAL_USERSTORE, ENTITY_NAME)" from this table using sql. How can I do that?

Update

I figured out that I can delete a unique constraint using following command if I have its constraint name.

"ALTER TABLE IDP_PROVISIONING_ENTITY DROP UNIQUE <CONSTRAINT NAME>"

Also these constraint names are available in the table "sysibm.systabconst" and also in "SYSCAT.TABCONST".

But the problem I still have is, how can I get the name of unique constraint by using the columns associated with it?

Upvotes: 1

Views: 4606

Answers (2)

hariprasad
hariprasad

Reputation: 585

I am using SYSCAT.KEYCOLUSE. Than I am able to drop unique constraint. Otherwise not. The key is in that to find out proper name of constraint. Unique constraint is compound from its own constraint with its name e.g. SQL190703165844530 and unique index, which has its different own name e.g. SQL190703165844444.

select * from SYSCAT.KEYCOLUSE where TABSCHEMA = <TABSCHEMA> and TABNAME = <TABNAME>;

Than it is possible to drop unique constraint and index together by clause :

alter table <TABLE_NAME> drop unique <CONSTRAINT_NAME>;

Upvotes: 0

Chamila Wijayarathna
Chamila Wijayarathna

Reputation: 1933

First run 

select INDNAME from SYSCAT.TABCONST WHERE TABNAME='IDP_PROVISIONING_ENTITY' AND COLNAMES='+ENTITY_TYPE+TENANT_ID+ENTITY_LOCAL_USERSTORE+ENTITY_NAME

Here as result you will get the constraint name: eg : SQL160215110206360

Then run 

ALTER TABLE IDP_PROVISIONING_ENTITY DROP CONSTRAINT SQL160215110206360

Upvotes: 2

Related Questions