3

I'm stuck on a little not understanding problem.

I'm using the MS SQL Managment Studio 2008. I have a brand new database with table X.

X has 3 Columns:

  • ID, uniqueidentifier, not null
  • username, nvarchar 50, not null
  • password, nvarchar50, not null

Now I save. Now I set the ID to primary key - no problem with saving.

Now I add a column: Email, nvarchar 50, not null - when I now try to save, I can't do it, because it tells me for saving the table must be deleted and new created.

I don't understand this, in SQL Server 2005 I'm sure, that is easy possible to add a row like this ?!

PassionateDeveloper
  • 14,558
  • 34
  • 107
  • 176
  • Can you **show us** the code (C#, VB.NET, T-SQL - whatever) you're using to save that row?? I quite honestly don't understand what you're really asking.... some code might help to understand what you're doing – marc_s Nov 15 '10 at 11:28
  • Oh yes, sorry, I mean columns, I will edit it. And of course I add columns. – PassionateDeveloper Nov 15 '10 at 11:40

3 Answers3

2

Saving changes after table edit in SQL Server Management Studio

Community
  • 1
  • 1
2

To clarify what Damien_The_Unbeliever said (EDIT: I now see that he has extended his reply), the first few rows you entered, before you added the NOT NULL constraint, won't have any value in the EMAIL column, and therefore the NOT NULL constraint would be violated. First, add the EMAIL column, allowing nulls. Then edit the existing rows so they have a value in that column. Then go back and add the NOT NULL constraint to the EMAIL column. Alternatively, you could provide (temporarily) a default value for EMAIL when you're defining the column, e.g. 'temp@somedomain.com', and existing rows would get that default value (as would new rows if no email was provided). Then you can go back and edit them.

The rule, simply put, is this: you cannot add a constraint to a table already populated with data, if one or more rows would violate the constraint.

A "constraint" is a rule you want the database to enforce.

Tim
  • 5,371
  • 3
  • 32
  • 41
1

You can't do this without also specifying what the default value is for the column - otherwise, how is SQL Server going to ensure the "not null" constraint on existing rows in the table?

If you try to do it via a query, you'll get much more explanation:

ALTER TABLE Table_2 ADD Email varchar(100) not null

Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'Email' cannot be added to non-empty table 'Table_2' because it does not satisfy these conditions.
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448