asked on

Create a random unique identifier when a record is comitted in a column that is not a primary key

I have a table that I'm trying to have the Primary Key (RegistrationID) and secondary field (RegistrationID2) both auto create a random unique identifier when a record is comitted.
Can I set this column to "Identity" even though it is not the Primary Key?
I'm getting an error " Cannot inser the value NULL into column 'RegistrationID2'; column does not allow nulls.

CREATE TABLE [dbo].[OrientationRegistration](
	[RegistrationID] [int] IDENTITY(1,1) NOT NULL,
 	[RegistrationID2] [uniqueidentifier] NOT NULL,
	[SubmitDate] [datetime] NULL,
	[PreferredName] [varchar](128) NULL,
	[BirthDate] [varchar](16) NULL,
	
 CONSTRAINT [UQ__Orientat__C85A786F2692810C] UNIQUE NONCLUSTERED 
(
	[RegistrationID2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Open in new window

Aneesh

you can set the default value as aNEWSEQUENTIALID() .. you can only have one identity column per table

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sameer2010

Is there a constraint that RegistrationID2 should not exist as RegistrationID somewhere?
While NEWID can be unique, it cannot eliminate possibility of duplicity however small. You can add unique constraint and handle violations in that case.

deighton

NEWID is UNIQUE, it is globally unique, not just unique for your table, but unique for all tables in all systems in the world.

javierpdx

ASKER

What is the correct syntax to add the default value NEWID().
I used this but below but the value is NULL when I commmit a record.

ALTER TABLE OrientationRegistration
Add RegistrationID2 uniqueidentifier not null default NEWID()

Thank you.

javierpdx

ASKER

Thanks. It worked. Just did not refresh for some reason.