SQL – Drop constraint without specifying its name

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.

Advertisements
This entry was posted in howtos and tagged , , , , , , , . Bookmark the permalink.

2 Responses to SQL – Drop constraint without specifying its name

  1. Adrian Smith says:

    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. electrobabe says:

    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 ;)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s