How can I change the value of a primary key of a table to be a random number?

Question

I have this table:

CREATE TABLE [dbo].[Word] (
    [WordId]        INT            NOT NULL,
    [Name]          VARCHAR (20)   NOT NULL,
    [StatusId]      INT            DEFAULT ((1)) NULL,
    [Syllables]     VARCHAR (20)   NULL,
    [Ascii]         AS             (ascii([Name])) PERSISTED,
    [CategoryId]    INT            DEFAULT ((1)) NOT NULL,
    [GroupId]       INT            DEFAULT ((1)) NOT NULL,
    [LessonId]      INT            DEFAULT ((1)) NOT NULL,
    [CreatedBy]     INT            DEFAULT ((1)) NOT NULL,
    [CreatedDate]   DATETIME       DEFAULT (getdate()) NOT NULL,
    [ModifiedBy]    INT            DEFAULT ((1)) NOT NULL,
    [ModifiedDate]  DATETIME       DEFAULT (getdate()) NOT NULL,
    [Version]       ROWVERSION     NULL,
    PRIMARY KEY CLUSTERED ([WordId] ASC),
    CONSTRAINT [FK_WordLesson] FOREIGN KEY ([LessonId]) REFERENCES [dbo].[Lesson] ([LessonId]),
    CONSTRAINT [FK_WordWordCategory] FOREIGN KEY ([CategoryId]) REFERENCES [dbo].[WordCategory] ([WordCategoryId]),
    CONSTRAINT [FK_WordWordGroup] FOREIGN KEY ([GroupId]) REFERENCES [dbo].[WordGroup] ([WordGroupId])
);


GO
CREATE NONCLUSTERED INDEX [Word_Category_IX]
    ON [dbo].[Word]([CategoryId] ASC);


GO
CREATE NONCLUSTERED INDEX [Word_Group_IX]
    ON [dbo].[Word]([GroupId] ASC);


GO
CREATE NONCLUSTERED INDEX [Word_Lesson_IX]
    ON [dbo].[Word]([LessonId] ASC);

How can I change the value of WordId to be a random number that is between 1 and the maximum value of the INT column?

Note that I understand there's a possibility of the random number being used twice but it's test data so I am not too concerned about that.

But why would you want to do that? What's the actual problem you're trying to solve? — Mureinik, Jun 23 '16 at 07:42
Why do you need it to be a random number? it's the PK so you want to make sure that you're not creating duplicates. This is exactly the use for a identity column. Also, you talk about the maximum value of the INT column, which INT column? — Rich Benner, Jun 23 '16 at 07:42
Shouldn't primary key be unique by definition (even in test data)? — lopushen, Jun 23 '16 at 07:42
Do you want to "change" the primary key to a random number? Or do you want it to be a random number when the row is inserted? — Jonathan Allen, Jun 23 '16 at 08:09
@lopushen Yes, the primary key(s) have to be unique. He can have duplicates in `WorldId` only if he makes a compound primary key such as `(WorldId, SomeOtherColumn)`. — Jonathan Allen, Jun 23 '16 at 08:10
Possible duplicate of [Generate random int value from 3 to 6](http://stackoverflow.com/questions/7878287/generate-random-int-value-from-3-to-6) — Ivan Starostin, Jun 23 '16 at 08:13
Agreed with everyone that primary keys should be unique but would like to do this for test data purposes. As the max value would be the max of int I think with the 100 rows of test data I have there should be almost no chance of a duplicate. Thanks — Samantha J T Star, Jun 23 '16 at 08:35
@SamanthaJ check my answer to see the calculation using rand() that should do this for you. — Rich Benner, Jun 23 '16 at 08:54
@RichBenner - Thanks but it's not that I want to find out how to do it with rand(). I really need to know how to change each row. Can I do this with just an update for every row or would I need a cursor or something like that ? — Samantha J T Star, Jun 23 '16 at 09:01
sure, UPDATE TableName SET FieldName = ROUND(RAND()*FieldName),0) — Rich Benner, Jun 23 '16 at 09:01

Rich Benner · Accepted Answer · 2016-06-23T09:04:01.443

I'm going to suggest a different approach to this than the route you are currently going down.

Just use an identity column, it's inbuilt and because this field is the Primary Key you need to ensure you don't get duplicates. The code would look like this.

CREATE TABLE [dbo].[Word] (
[WordId]        INT            IDENTITY(1,1) NOT NULL,

It will give you a value starting with 1 that increments by 1 each time a new row of data is entered.

Also, looking at your code, you've got the field StatusId as nullable but with a default value, are you sure that you don't want this as a NOT NULL field?

For information, you can use this calculation to get a random number less than a given int value;

DECLARE @RandomInt int; SET @RandomInt = 42

SELECT
@RandomInt Number
,ROUND(RAND()*@RandomInt,0) RandomLessThanInt

You'll get an answer like this;

Number  RandomLessThanInt
42      15

It will obviously change every time it's run. You'd have to ensure that the number didn't already exist otherwise you will be attempting to violate the PK constraint and the insert will fail.

If you already have the table populated with data then you could do this

UPDATE TableName
SET FieldName = ROUND(RAND()*FieldName,0)

Thanks Rich, but I moved away from using IDENTITY as I need this to be a random number for testing. It was using IDENTITY yesterday. Just not sure how to change to random today. — Samantha J T Star, Jun 23 '16 at 09:02
@SamanthaJ i've just updated the answer to include the update statement you could run on your table. Might take a couple of attempts depending upon whether you get conflicting values. — Rich Benner, Jun 23 '16 at 09:04

How can I change the value of a primary key of a table to be a random number?

1 Answers1