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?

Improve this question
3
  • You do know that the entire SQL Server documentation is available online and for free? Just head on over and look up ALTER TABLE .... Commented Jul 7, 2013 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. Commented Apr 29, 2015 at 22:10
  • learn.microsoft.com/en-us/sql/t-sql/statements/… 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 (( Commented Mar 1, 2018 at 10:02

1 Answer 1

Reset to default
19

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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.
ok you cannot alter if you have unique constraint on that column. drop that and recreate it

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.