Invalid Identifier in ALTER TABLE ADD COLUMN

Question

I'm getting the error "Invalid Identifier" whilst running this ALTER TABLE statement:

ALTER TABLE TRUCK ADD COLUMN WEIGHT INTEGER NOT NULL;

The syntax is like this, so I don't understand the error:

ALTER TABLE table_name ADD COLUMN column_name data_type[NOT NULL][UNIQUE]

The attribute WEIGHT doesn't need unique.

How do I include the "not greater than 1000" for the Integer data type into the Alter statement?

What database are you using? Some don't permit the keyword `column` after `add`. — Gordon Linoff, Nov 06 '14 at 16:42
and as there isn't a column COLUMN in your table you'll get this error... Or, it could be that TRUCK doesn't exist? What is the exact error message - copy and paste it into your question (you can [edit] it) — Ben, Nov 06 '14 at 16:42
@Ben i've already created the table Truck and i just need to add 2 columns to the table that's it. somehow i got the error 'Invalid Identifier' and now i got another error >. — Preeyah, Nov 06 '14 at 16:51
@GordonLinoff i'm using Integer datatype and shouldn't be greater than 1000 — Preeyah, Nov 06 '14 at 16:52

score 3 · Accepted Answer · edited Nov 06 '14 at 18:25

3

SQL Server:

 ALTER TABLE TRUCK
 ADD WEIGHT INT NOT NULL

edited Nov 06 '14 at 18:25

marc_s

answered Nov 06 '14 at 16:46

user2366842

Hey, i typed the statement, i got another error : table must be empty to add mandatory (NOT NULL) column.. i don't understand why this so.. why i have to delete other columns.. – Preeyah Nov 06 '14 at 16:48
try adding a semicolon after it, I'm not sure of the database you're using. Edit: what it's complaining about is that there are existing rows that won't have a value assigned to it after adding the new row. best solution is to skip the not null, add values for existing rows, and if it really is a not null value, set to not null later...if the value possibly CAN be null though, just leave it as it is, else you'll end up with headaches down the line. – user2366842 Nov 06 '14 at 16:49
Feel free to mark as accepted answer if it helped you out. Usually you want to set NOT NULL when setting up the database initially to avoid things like this, however I understand that you can sometimes be thrown into maintaining existing databases... – user2366842 Nov 06 '14 at 16:57
Also as far as keeping the value under 1000, personally i think it should be checked programmatically before the data hits the database at all, as that seems like business logic. Unless you are absolutely backed into a corner you don't want to mix business logic with your data layer. – user2366842 Nov 06 '14 at 17:04

score 0 · Answer 2 · edited Nov 06 '14 at 18:25

0

This query will be helpful.

alter table truck 
  add column weight int not null default 0 check (weight < 1000)

edited Nov 06 '14 at 18:25

marc_s

answered Nov 06 '14 at 16:57

Saravana Kumar

2 Answers2