CODEWITHSUNDEEP
c#sql-serverentity-frameworkconstraintsguid
Dindar
Dindar

Reputation: 3235

Adding guid default value constraint to sql primary key not working

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;}
}

Upvotes: 1

Views: 3720

Answers (2)

Stan
Stan

Reputation: 983

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()

Upvotes: 0

Backs
Backs

Reputation: 24903

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: property mapping

Upvotes: 1

Related Questions