1

I've added a column to an existing table like that:

ALTER TABLE [dbo].[tbl_exampleTable] 
    ADD ExampleName varchar(1) NOT NULL DEFAULT('w');

It successfully added this column.

But when I try to drop this column, like that:

ALTER TABLE [dbo].[tbl_exampleTable] 
    DROP COLUMN ExampleName;

I can't do this, and every time I see the error like that, but I'm sure that there is no procedures or other things that can use this column.

ALTER TABLE DROP COLUMN ExampleName failed because one or more objects access this column.

How can I drop this column?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dimitri
  • 109
  • 6
  • 3
    Because you created the column with a `DEFAULT` `CONSTRAINT`. You must drop that `CONSTRAINT` first, then you can `DROP` your column. What the name of that `CONSTRAINT` is, well, you'll need to find out; this is why you should *always* name your `CONSTRAINT`s when you make them. – Thom A Jan 20 '22 at 16:44
  • 1
    @Larnu How can I find out name of this ```CONSTRAINT```? – dimitri Jan 20 '22 at 16:49
  • 3
    You can narrow it down pretty quickly with `SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'dbo.tbl_exampleTable');` – Aaron Bertrand Jan 20 '22 at 16:50
  • Alternatively, you can use the Object Explorer in SSMS/ADS to find the name(s). – Thom A Jan 20 '22 at 16:52
  • 2
    If you had **explicitly named** your `DEFAULT` constraint when you created it in the first place - you wouldn't have to go searching for its system-provided name now .... just sayin' .... – marc_s Jan 20 '22 at 16:52
  • How can I explicitly name my constraint while adding column to table @marc_s ? – dimitri Jan 20 '22 at 16:53
  • 2
    `ALTER TABLE [dbo].[tbl_exampleTable] ADD ExampleName varchar(1) NOT NULL CONSTRAINT DF_ExampleName DEFAULT('w');` @dimitri . – Thom A Jan 20 '22 at 16:54
  • Also, I would probably suggest to use of `char(1)` rather than `varchar(1)`; there's no need for the added overhead of the variable length when it's one character long. – Thom A Jan 20 '22 at 16:57
  • How then can I drop this column if I know name of the CONSTRAINT? – dimitri Jan 20 '22 at 17:03
  • I am not sure your problem is just the default constraint. Otherwise it would tell you the constraint name already in the error message. If you want to drop the constraint and try, then: ALTER TABLE [dbo].[tbl_exampleTable] DROP CONSTRAINT ; – Cetin Basoz Jan 20 '22 at 19:18

0 Answers0