Error while dropping a column

Question

I just created a table as below in MS SQL

create table Employee 
(emp_id smallint not null,
employee_name varchar (30) not null,
salary money not null,
department varchar(30),
address varchar(40),
primary key(emp_id)
)

After creating the table, I feel like auto populating the emp_id column( using Identity). So, I was trying to drop the column emp_id as below:

alter table Employee 
drop column emp_id

Even though, I haven't inserted any rows in the table yet, I am getting the error

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

Please help!!!

HI it is because the column you are doping has been referenced in other table . If you really want to drop it please first drop the constraint and then drop the column . — Yashveer Singh, Jan 17 '17 at 08:41
`emp_id` is your primary key as in your create table query `primary key(emp_id)` so you should drop that Primary Key constraint before drop that column — Pham X. Bach, Jan 17 '17 at 08:43
Possible duplicate of [How to drop column with constraint?](http://stackoverflow.com/questions/8641954/how-to-drop-column-with-constraint) — Drag and Drop, Jan 17 '17 at 08:50
TIP: When adding constraints such as your PK, it helps to give it an explicit name rather than allowing a derived random name to be generated. — Disillusioned, Jan 17 '17 at 08:55
In fact there is 43 question with "ALTER TABLE DROP COLUMN failed because one or more objects access this column.". — Drag and Drop, Jan 17 '17 at 09:01

score 2 · Answer 1 · answered Jan 17 '17 at 08:43

2

Something like this can help .

   ALTER TABLE Employee   
   DROP CONSTRAINT PK__Employee__1299A86183CA4FBC; 


   alter table Employee 
   drop column emp_id

answered Jan 17 '17 at 08:43

Yashveer Singh

1,914
2
15
23

So the Idea is first drop all constraint like primary keys / FK etc and then alter the table – Yashveer Singh Jan 17 '17 at 08:45
NOTE: When re-adding the column, you'd need to re-add the PK as well. – Disillusioned Jan 17 '17 at 08:53
@CraigYoung :- Thanks I missed to inform that it will make answer more useful . – Yashveer Singh Jan 17 '17 at 08:59

score 0 · Answer 2 · answered Nov 18 '20 at 18:05

I solved the problem by executing below query: I need to remove a column and all the entries from that column to free my DB size my initial table structure is as shown below:

CREATE TABLE words(_id,word,synonyms,favorite,history,updDate)

And I wanted the table in below form

CREATE TABLE words(_id,word,favorite,history,updDate)

So I executed below query and it removed "synonyms" column

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(_id,word,favorite,history,updDate);
INSERT INTO t1_backup SELECT _id,word,favorite,history,updDate FROM words;
DROP TABLE words;
CREATE TABLE words(_id,word,favorite,history,updDate);
INSERT INTO words SELECT _id,word,favorite,history,updDate FROM t1_backup;
DROP TABLE t1_backup
COMMIT;

Error while dropping a column

2 Answers2