How to drop a constraint

Troubleshooting

Problem

When trying to drop a constraint, you get an error that this is not supported. How do you drop a constraint?

Symptom

When trying to drop a primary key constraint, you might try using the following SQL:

TPCDS40B(ADMIN)=> alter table inventory drop primary key cascade;

The following error appears:

ERROR: This is not supported in Netezza (Oracle feature).To drop a constraint, specify the constraint name

Resolving The Problem

1. To identify the constraint name, describe the table with \d <table name> and identify the constraint you wish to drop. For example:

TPCDS40B(ADMIN)=> \d inventory

Table "INVENTORY"

Attribute | Type | Modifier | Default Value

----------------------+----------+----------+---------------

INV_DATE_SK | INTEGER | NOT NULL |

INV_ITEM_SK | INTEGER | NOT NULL |

INV_WAREHOUSE_SK | INTEGER | NOT NULL |

INV_QUANTITY_ON_HAND | INTEGER | |

ORIG_INV_ITEM_SK | INTEGER | NOT NULL |

DSID | SMALLINT | |

Constraints:

Name Settings

INVENTORY_PK Primary key INV_DATE_SK,INV_ITEM_SK,INV_WAREHOUSE_SK)

Distributed on hash: "ORIG_INV_ITEM_SK"

2. Now that the name associated with the constraint has been identified, you can drop the constraint using the SQL shown below:

TPCDS40B(ADMIN)=> alter table inventory_ordered drop constraint INVENTORY_PK restrict;

[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

NZ120798

Was this topic helpful?

Document Information

More support for:
IBM PureData System

Software version:
1.0.0

Document number:
463543

Modified date:
17 October 2019

UID

swg21571324

IBM Support

Tips