CODEWITHSUNDEEP
sql-server
Samantha J T Star
Samantha J T Star

Reputation: 32808

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

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.

Upvotes: 0

Views: 314

Answers (1)

Rich Benner
Rich Benner

Reputation: 8113

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)

Upvotes: 1

Related Questions