12

I have a uniqueidentifier column in a table and it's not a key or index. I want to set the default value using NEWSEQUENTIALID(), but I get the error:

Error validating default for column..

But if I use NEWID() no error. What's going on?

EDIT: you can just ignore the error dialog and continue.

P a u l
  • 7,805
  • 15
  • 59
  • 92
  • In SQL2014, you need to use the datatype as "uniqueidentifier" in order to use the newsequentialid() method and you will not see any error messages. – HPWD Jan 12 '17 at 19:19

4 Answers4

13

Quoting accepted answer of this question.

Workaround: create your table without specifying any default, and then type in this T-SQL statement in a normal query window and run it:

ALTER TABLE dbo.YourTable
    ADD CONSTRAINT DF_SerialID DEFAULT newsequentialid() FOR SerialID
Community
  • 1
  • 1
programmer
  • 3,043
  • 1
  • 22
  • 30
  • The same thing happened to me when I tried to add a 'unique key' index on an existing uniqueidentifier column that had already been populated with its default value of 'newsequentialid'. When I saved the table, I got the same warning, but as P a u l mentioned, you can just click 'yes' to ignore the warning and the table will be saved successfully. I can't think of a circumstance or reason why it wouldn't succeed. It shouldn't be calling the default anyway if it's just updating a table. – Triynko Nov 02 '13 at 00:21
  • I had trouble at first figuring out what to change to make it work for me. I find it is ALTER TABLE dbo.YourTable ADD CONSTRAINT DF_COLUMN DEFAULT newsequentialid() FOR COLUMN – Karl Henselin May 08 '14 at 00:37
9

This is a bug in the designer that is not being fixed by microsoft.

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/cad8a4d7-714f-44a2-adb0-569655ac66e6

P a u l
  • 7,805
  • 15
  • 59
  • 92
0

according to the documentation: http://msdn.microsoft.com/en-us/library/ms189786.aspx Remarks section:

... When NEWSEQUENTIALID() is used in DEFAULT expressions, it cannot be combined with other scalar operators. For example, you cannot execute the following:

CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT dbo.myfunction(NEWSEQUENTIALID())) ...

Does that answer your question?

  • No, I am using NEWSEQUENTIALID() by itself in the default field in the table designer in sql server management studio. NEWID() works however and I just wanted to know why. – P a u l Jun 19 '09 at 03:35
  • I see what you mean, I just tested that and when setting NEWSEQUENTIALID() as the default value in SSMS 2008 I get an error but following SQL "CREATE TABLE [dbo].[Table_1]([col1] [int] NOT NULL, [uuid] [uniqueidentifier] NOT NULL DEFAULT (NEWSEQUENTIALID())) ON [PRIMARY]" executes just fine and there is no error while editing the table later on. Looks like an SSMS bug methinks. –  Jun 19 '09 at 10:09
0

That is incorrect.

follow the steps here :

http://www.geekinterview.com/question_details/31705

Pieter
  • 9
  • 1