0

We have to do a task for school and I can't get it to work.

I am using Microsoft SQL Server Management studio. I have a table Firmen and I would like to add a column Firma to it, defined as a text with max length of 50 characters.

To my understanding, the syntax to do this is

USE Schueler;

ALTER TABLE Firmen 
    ADD COLUMN Firma nvarchar(50) NOT NULL;

But I get this error

Falsche Syntax in der Nähe des COLUMN-Schlüsselworts.

Translating into

Wrong Syntax near COLUMN keyword

I don't get what I am doing wrong.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Janick.Spielmann
  • 35
  • 4
  • 1
    A new column can't be NOT NULL without having a default value. – jarlh Dec 19 '22 at 09:42
  • 3
    It can when the table is empty. The error showing is incorrect syntax. – Peter Dec 19 '22 at 09:42
  • 3
    Step 1 would be to **consult the [official MS documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16)** on `ALTER TABLE` -..... it shows you should use `ALTER TABLE (table) ADD (column name) ` - no `COLUMN` keyword ..... – marc_s Dec 19 '22 at 09:46
  • I wish they would just make that an optional keyword here. It would then be consistent with `ALTER COLUMN` and `DROP COLUMN` – Martin Smith Dec 19 '22 at 11:17

1 Answers1

2

You don't need to add the COLUMN keyword. it should be:

ALTER TABLE Firmen ADD Firma nvarchar(50) NOT NULL;

Edit

As jarlh mentioned. When the table already has data you have to make the column NULLABLE, use a default value, or make it NULLABLE, fill it and ALTER it again to make it not NULLABLE.

Peter
  • 475
  • 1
  • 8
  • You would be better off `DROP`ing the `CONSTRAINT` afterwards than doing an `UPDATE` on the *entire* table. Especially on Enterprise. – Thom A Dec 19 '22 at 09:46
  • I think that depends on the situation. Dropping the constraint will enable the column to have NULL values. Perhaps that is not wanted. Or am I understanding your comment wrong? – Peter Dec 19 '22 at 09:49
  • If the column is defined as `NOT NULL` it can't have `NULL` values, @Peter. A `DEFAULT` `CONSTRAINT` doesn't stop a column having a `NULL` value if it's defined as `NULL`. – Thom A Dec 19 '22 at 09:52
  • Ah, when rereading my edit, it was a bit unclear indeed. What I ment, we have 3 options, 1 make nullable, 2 keep not null, but add a default value or 3, make with nullable, add data, make not nullable. – Peter Dec 19 '22 at 10:02
  • Where 1 and 2 are only meta data, 3 can be an intensive update – Peter Dec 19 '22 at 10:03
  • 1
    Yes, but if you `ADD` the column *with* a `DEFAULT` value, and then `DROP` the `CONSTRAINT` then it's meta data only (on enterprise), regardless of if the column is defined as `NULL`able or not. – Thom A Dec 19 '22 at 10:05
  • Ah, understand you now, thanks! :) – Peter Dec 19 '22 at 10:09