0

in orther to get a column similar to the mysql ENUM type, I wrote a sql query as follows

ALTER TABLE [DbName].[dbo].[MediaContent]
ADD MediaType nvarchar(50) 
check(MediaType in ('audio','video','song','other'))

this worked as wished(for test): But now I want to delete this column without success. It seems like there no way to directly delete a column which has a constraint up on it. How can I solve this issue? I want to delete this column and create another one.

here is the error message I get while the deletion

 The object 'CK__MediaCont__Media__14270015' is dependent on column 'MediaType'.
  ALTER TABLE DROP COLUMN MediaType failed 
because one or more objects access this         
column. (Microsoft SQL Server, Error: 5074)
Bellash
  • 7,560
  • 6
  • 53
  • 86
  • 2
    Have you tried dropping the constraint first and then dropping the column? – ron tornambe Feb 20 '14 at 16:31
  • Thank you for this answer, am I missing something? where do I find this constraint in order to drop it? – Bellash Feb 20 '14 at 16:42
  • Done! thank you. USed this `ALTER TABLE [DbName].[dbo].[MediaContent] DROP CONSTRAINT CK__MediaCont__Media__14270015` as suggested by ron tornambe – Bellash Feb 20 '14 at 16:52

3 Answers3

4

The object referenced in the error message is the name of the constraint. You should be able to use the follow:

ALTER TABLE [DbName].[dbo].[MediaContent]
  DROP CONSTRAINT CK__MediaCont__Media__14270015
ron tornambe
  • 10,452
  • 7
  • 33
  • 60
1

You need to first drop the check constraint mentioned in the error message since that's stopping you from dropping the column. Following that you may drop the column.

shree.pat18
  • 21,449
  • 3
  • 43
  • 63
0

Drop the constrain first then drop the column ,it will work

Madara Uchiha
  • 128
  • 7