CODEWITHSUNDEEP
sqlsql-serverentity-frameworkt-sqluniqueidentifier
Ms. B
Ms. B

Reputation: 1133

SQL UniqueIdentifier

I have a table called UserCredentials and it has a uniqueidentifier column [UserCredentialId]. When I try to create a new user, I get 00000000-0000-0000-0000-000000000000 in my first try, then when I try adding another user, it says PK cannot be duplicated. At first I had a hard time guessing what does it mean but, I think its because of my uniqueidentifier is not generating random id.

What to do?

EDIT

Here is my SQL table structure:

CREATE TABLE [dbo].[UserCredential](
    [UserCredentialId] [uniqueidentifier] NOT NULL,
    [UserRoleId] [int] NOT NULL,
    [Username] [varchar](25) NOT NULL,
    [Password] [varchar](50) NOT NULL,
    [PasswordSalt] [varchar](max) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [PayorCode] [varchar](20) NOT NULL,
    [ProviderCode] [varchar](50) NULL,
    [CorporationCode] [varchar](50) NULL,
    [Department] [varchar](50) NULL,
    [Status] [varchar](1) NOT NULL,
    [DateCreated] [datetime] NOT NULL,
    [DateActivated] [datetime] NULL,
    [Deactivated] [datetime] NULL,
    [DateUpdated] [datetime] NULL,
    [CreatedBy] [varchar](50) NOT NULL,
    [UpdatedBy] [varchar](50) NOT NULL,
    [EmailAddress] [varchar](50) NULL,
    [ContactNumber] [int] NULL,
    [Picture] [varbinary](max) NULL,
 CONSTRAINT [PK_UserCredential_1] PRIMARY KEY CLUSTERED 
(
    [UserCredentialId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I already set it to (newid()) but still not working.

Upvotes: 0

Views: 1886

Answers (3)

qxg
qxg

Reputation: 7036

Change your table definition to 

[UserCredentialId] UNIQUEIDENTIFIER DEFAULT (NEWSEQUENTIALID()) NOT NULL

Check why prefer NEWSEQUENTIALID than newid at http://technet.microsoft.com/en-us/library/ms189786.aspx.

When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

Upvotes: 1

Toan Nguyen
Toan Nguyen

Reputation: 11581

Set the Id of your user instance to Guid.NewGuid();

user.Id = Guid.NewGuid();

Upvotes: 2

Ross Bush
Ross Bush

Reputation: 15155

You can have all values in the table default to a new value during insert, much like an IDENTITY.

Run this to set the default of all inserts to use newid().

ALTER TABLE [dbo].[UserCredential] ADD  CONSTRAINT [DF_UserCredential_UserCredentialID]  DEFAULT (newid()) FOR [UserCredentialId]
GO

Upvotes: 0

Related Questions