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

javierpdxAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
if you want that to be alway random you should set the default value as NEWID()
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can set the default value as aNEWSEQUENTIALID() .. you can only have one identity column per table
0
 
sameer2010Commented:
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.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
deightonprogCommented:
NEWID is UNIQUE, it is globally unique, not just unique for your table, but unique for all tables in all systems in the world.
0
 
javierpdxAuthor Commented:
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.
0
 
javierpdxAuthor Commented:
Thanks. It worked.  Just did not refresh for some reason.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.