1

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 ?

Improve this question
5
  • 1
    You need to drop the unique constraint first, and then the column. Commented Jan 8, 2021 at 14:11
  • @GordonLinoff I tried I get: 'person' is not a constraint. Commented Jan 8, 2021 at 14:18
  • 1
    UQ__informat__DC4560C2776204D1 is a constraint you need to remove first Commented Jan 8, 2021 at 14:19
  • @GuidoG Thank you. And thats really very complicated to know whats the code before I actually get this error. Commented Jan 8, 2021 at 14:24
  • 1
    The reason it looks "complicated" is because you didn't name your CONSTRAINT, @Ileh . It's good habit to name them, so that you don't get "unfriendly" names like that for your objects. Commented Jan 8, 2021 at 14:31

1 Answer 1

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

It doesn't need to be a separate statement ALTER TABLE new_info DROP UQ__informat__DC4560C2776204D1 , COLUMN person; would also work