CODEWITHSUNDEEP
sqlsql-serversql-server-2008alter-tabledrop
user13279074
user13279074

Reputation:

can't DROP COLUMN - MS SQL

I just created a new table and I want to remove the 'person' column in it but I get an error -

CREATE TABLE new_info(
info_id INT IDENTITY (1,1) PRIMARY KEY,
title VARCHAR(500) NOT NULL,
person VARCHAR(50) NOT NULL UNIQUE
)

And after this -

ALTER TABLE new_info
DROP COLUMN person;

I get this error -

Msg 5074, Level 16, State 1, Line 1
The object 'UQ__informat__DC4560C2776204D1' is dependent on column 'person'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN person failed because one or more objects access this column.

Any Idea why, or what's causing this ?

Upvotes: 1

Views: 2957

Answers (1)

GuidoG
GuidoG

Reputation: 12039

Drop the unique constraint first before you can drop the column This is why it is always best to create named constraint

ALTER TABLE new_info DROP CONSTRAINT UQ__informat__DC4560C2776204D1

and then you can drop your column. You can also do it in one statement, as commented by Martin

ALTER TABLE new_info DROP UQ__informat__DC4560C2776204D1, 
                          COLUMN person

Better to create tables like this

CREATE TABLE new_info(
  info_id INT IDENTITY(1,1),
  title VARCHAR(500) NOT NULL,
  person VARCHAR(50) NOT NULL,

 constraint PK_new_info_InfoID primary key (info_id),
 constraint IX_new_info_person UNIQUE (person)
)

now you will at least get better info in your errormessages

Upvotes: 2

Related Questions