Adding guid default value constraint to sql primary key not working

Question

I have an empty table that has two column : Id and Title .

The Id is guid type , Primary key , so Not Nullable .

I try to add a constraint for default value of Id , constraint is like this :

ALTER TABLE [dbo].[TestTable] ADD  CONSTRAINT [DF_TestTable_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO

In Entity Framework , Because Id is not nullable , It fill the Id with '00000000-0000-0000-000000000000' value .

Therefor my constraint in sql doesn't work property for creating new sequentialId() and my first record in table get '00000000-0000-0000-000000000000' value. And it's so obvious for the second row it get the:

"Vaiolation of PRIMARY KEY Constraint "PK_TestTable". Cannot insert Duplicate Key" error .

I know I could generate guid in .NeT like Guid.NewGuid() and fill the Id with value , But I'm trying to delegate this responsibility to sql and newsequentialId() default value .

I'm wonder how could I do that ?

For full info i put the entire script here :

CREATE TABLE [dbo].[TestTable](
    [Id] [uniqueidentifier] NOT NULL,
    [Title] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TestTable] ADD  CONSTRAINT [DF_TestTable_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO

Update: My EF database first approach Mapping is:

public partial class TestTable 
{
    [DatabaseGenerated(DatabaseGenereatedOption.Computed)]
    public System.Guid Id {get ;set;}
    public string Title {get; set;}
}

possible duplicate of [How to set NewId() for GUID in entity framework](http://stackoverflow.com/questions/18200817/how-to-set-newid-for-guid-in-entity-framework) — Andrew Savinykh, Aug 09 '15 at 11:52

Backs · Accepted Answer · 2015-08-09T11:58:11.893

1

Map your property Id as Computed like this:

Property(o => o.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

OR

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }

OR, if you have edmx you must edit property in designer:

edited Aug 09 '15 at 11:58

answered Aug 09 '15 at 11:23

Backs

24,430
5
58
85

Thanks for the Idea , But I try it , and it didn't work , Still the same error – Mostafa Aug 09 '15 at 11:31
I Update the code , Because I use the Database First Approach I don't have Mapping code, Just putting attribute on my model . – Mostafa Aug 09 '15 at 11:42
@Mostafa you have edmx, and you should update Id property in designer, I edited my answer – Backs Aug 09 '15 at 11:49
Thanks for the idea , I changed the StoreGeneratedPattern to Identity (it's not by default ) and worked (I don't know why), Please correct your answer so I could accept your answer . – Mostafa Aug 09 '15 at 11:55
1

@Mostafa it's primary key, of couse. I edited answer – Backs Aug 09 '15 at 11:59

score 0 · Answer 2 · answered Aug 09 '15 at 13:31

0

Unless you have some requirement to do this the hard way:

ALTER TABLE [dbo].[TestTable] ADD  CONSTRAINT [DF_TestTable_Id] 
    DEFAULT (NEWID()) FOR [Id]

NEWID() is the TSQL equivalent of GUID.NewGuid()

answered Aug 09 '15 at 13:31

Stan

985
1
7
12

Adding guid default value constraint to sql primary key not working

2 Answers2