SQL – Drop constraint without specifying its name

howtos 1 Minute

Drop a UNIQUE constraint in relational databases like Oracle without specifying its name by referencing the column or columns:

ALTER TABLE Employee DROP UNIQUE (name)

.

But if you want to drop a NOT NULL constraint  without specifying its name you have to use a workaround, if there is an index on the column:

ALTER TABLE Employee ADD (name_ varchar(32));
update Employee set name_ = name;
ALTER TABLE Employee DROP COLUMN name;
ALTER TABLE Employee ADD (name varchar(32));
update Employee set name = name_;
ALTER TABLE Employee DROP COLUMN name_;

.

To drop a NOT NULL constraint in Oracle you can MODIFY an unindexed column:

ALTER TABLE Employee MODIFY name NULL;

.

ps. Dieses Mal ein How-To-Posting aus meinem spannenden Leben als Software-Entwicklerin in Englisch, zum leichteren Wiederfinden in Suchmaschinen.

Advertisement

Published by electrobabe

Published

2 thoughts on “SQL – Drop constraint without specifying its name”

  1. Hey that’s totally cool about the dropping the unique constraint. I didn’t know that. By the way, I assume you are talking about Oracle? :)

    To drop a NOT NULL constraint in Oracle you can MODIFY the column:

    ALTER TABLE Employee MODIFY name NULL;

    Note that in Oracle MODIFY really does just do modifications. You get an error if the changes you are doing are already applied, and you only have to specify the aspects you want to change (i.e. no type VARCHAR in the above MODIFY statement).

    ORA-01451: column to be modified to NULL cannot be modified to NULL

    In MySQL you have to specify the whole type, e.g. VARCHAR(32) NULL.

    I find these differences quite annoying :) I would like a) to just specify the bits I want to change b) if the changes are already in place, then just silently ignore them. I mean saying I want a column “NULL” is for me a command like “I want the column to be NULL after the statement”, it’s not an assertion “I totally believe the column was NOT NULL to being with”

  2. You are totally right with your comment. the problem I faced at work was an index on the column I wanted to drop. therefore the constraint was not dropped with a MODIFY statement, but with the DROP COLUMN statement.
    I think, I need to do a litte more research before posting ;)

Comments are closed.