7

I've setup a table in SQL Server 2008 Express and forgot to add a not null constraint to my unique recordid column. I tried to add it afterward, with this statement:

alter table movie_archive alter column RecordID Not null;

but it gives me an error message, saying there's a syntax error at "not". What am I doing wrong?

ruakh
  • 175,680
  • 26
  • 273
  • 307
kyle5385
  • 111
  • 2
  • 3
  • 7
  • You ***do know*** that the entire SQL Server documentation is available online and for free? [Just head on over and look up `ALTER TABLE` ....](http://msdn.microsoft.com/en-us/library/ms130214.aspx) – marc_s Jul 07 '13 at 17:25
  • FWIW, the second question listed as a dupe does not answer this question at all. It also pertains to MySQL, not SQL Server. – Lambart Apr 29 '15 at 22:10
  • https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql is a direct link to ALTER TABLE spec. There is a note about a necessity of using a column type when altering a column to NULL or NOT NULL. But without any reasons (( – Nashev Mar 01 '18 at 10:02

1 Answers1

17

specify the datatype of the column

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL;

alter table movie_archive alter column RecordID INTEGER Not null;
chetan
  • 2,876
  • 1
  • 14
  • 15
  • i am still getting the same error message for this problem is there anything else you need to know before you can make a more informed answer? alter table movie_archive alter column RecordID INTEGER Not null; This is what i put in. Msg 5074, Level 16, State 1, Line 1 The object 'UQ__Movie_Ar__FBDF78C84C0144E4' is dependent on column 'RecordID'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN RecordID failed because one or more objects access this column. this is the error i got. – kyle5385 Jul 07 '13 at 17:36
  • 4
    ok you cannot alter if you have unique constraint on that column. drop that and recreate it – chetan Jul 07 '13 at 17:39