CODEWITHSUNDEEP
sqlsql-server
Varsha
Varsha

Reputation: 19

Error while dropping a column

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!!!

Upvotes: 0

Views: 3379

Answers (2)

Jasmine John
Jasmine John

Reputation: 921

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;

Upvotes: 0

Yashveer Singh
Yashveer Singh

Reputation: 1977

Something like this can help .

   ALTER TABLE Employee   
   DROP CONSTRAINT PK__Employee__1299A86183CA4FBC; 


   alter table Employee 
   drop column emp_id

Upvotes: 2

Related Questions