0
9
Question Davi Massaru Teixeira Muta · 1 hr ago

REMOVE | unique not null | DDL |

#InterSystems IRIS #SQL

How can I remove UNIQUE NOT NULL from an existing column via DDL?

I currently have a table in production that was originally created via DDL, where the column was defined as UNIQUE NOT NULL:

CREATE TABLE database.Books ( ID %Library.UniqueIdentifier PRIMARY KEY, TagExternal VARCHAR('') UNIQUE NOT NULL, Status VARCHAR(20) NOT NULL, [ ... ] );  

I now need to change this column so that it is no longer UNIQUE and no longer NOT NULL, making it equivalent to:

CREATE TABLE database.Books ( ID %Library.UniqueIdentifier PRIMARY KEY, TagExternal VARCHAR(''), Status VARCHAR(20) NOT NULL, [ ... ] );

I need to execute this through SQL because my product uses Liquibase, and I cannot determine the exact name of the index that needs to be removed. I tried running the following, but without success:

DROP INDEX

(SELECT INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_SCHEMA = 'database' AND TABLE_NAME = 'Books' AND COLUMN_NAME = 'TagExternal')

ON TABLE database.Books

 

What is the correct way to remove the UNIQUE and NOT NULL constraints from an existing column using DDL in InterSystems IRIS?

Product version: IRIS 2022.1
$ZV: IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2022.1.3 (Build 670_1_23145U) Thu Feb 22 202
Discussion (1)0
Log in or sign up to continue

Comments

DC AI Bot · 1 hr ago Bot

To remove the UNIQUE and NOT NULL constraints from an existing column using DDL in InterSystems IRIS, you need to use the ALTER TABLE command with syntax to drop the constraints. However, dropping a UNIQUE constraint requires knowing its defined name at the time of table creation. Once identified, you can drop it as such:

  1. Drop UNIQUE Constraint:

    ALTER TABLE database.Books DROP CONSTRAINT constraint_name;

    Here, constraint_name is the name of the unique constraint set on the TagExternal column. If this name isn’t explicitly known, check the schema’s catalog details for the table to find it.

  2. Modify Column to Allow NULL Values:

    ALTER TABLE database.Books MODIFY TagExternal VARCHAR('');

    This alters the column to remove the NOT NULL constraint.

Ensure you retrieve the exact constraint name and verify the schema catalog details where these constraints might be referenced 12.

For further assistance, determine the full index definition for your column by querying the catalog or inspecting the table metadata.


Sources:

Tell us more:
Don't like the style
Not factually correct
Didn't fully follow instructions
Refused when it shouldn't have
Being lazy
More...
Submit
0 0