• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

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

0
javierpdx
Asked:
javierpdx
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can set the default value as aNEWSEQUENTIALID() .. you can only have one identity column per table
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
if you want that to be alway random you should set the default value as NEWID()
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
deightonCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now