large volume test data - PK constraint

Question

large volume test data - PK constraint

etl2016

Default port

Points: 1459
More actions
August 8, 2018 at 2:27 pm

#407661

Hi
I have a table with 4-column composite PK.
What is the best way to generate high volume test data without violating PK constraint? Use RAND sort of random generator to insert test data?
Will this plan work - introduce a 5th column and make it a 5-column PK, with the new 5th column as an IDENTITY, so that always the 5-part key is unique?
thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

etl2016 - Wednesday, August 8, 2018 2:27 PM
Hi
I have a table with 4-column composite PK.
What is the best way to generate high volume test data without violating PK constraint? Use RAND sort of random generator to insert test data?
Will this plan work - introduce a 5th column and make it a 5-column PK, with the new 5th column as an IDENTITY, so that always the 5-part key is unique?
thank you

Well if just adding an identity is an option it seems like the uniqueness of the real primary key isn't an issue anyways so why don't you just drop the PK?

ScottPletcher SSC Guru Points: 100949 More actions · Answer 2

Yes, for testing purposes, adding an identity to guarantee uniqueness should work just fine.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

autoexcrement SSCertifiable Points: 5951 More actions · Answer 3

or just dump a guid into each field in your pk.

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

andycadley SSCertifiable Points: 5296 More actions · Answer 4

If you don't want to change your PK (and one would assume not) it is usually easier to generate a list of unique PK valued first (which can just be a DISTINCT over random data) and then join that onto suitable random values for all the other columns.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

etl2016 - Wednesday, August 8, 2018 2:27 PM
Hi
I have a table with 4-column composite PK.
What is the best way to generate high volume test data without violating PK constraint? Use RAND sort of random generator to insert test data?
Will this plan work - introduce a 5th column and make it a 5-column PK, with the new 5th column as an IDENTITY, so that always the 5-part key is unique?
thank you

This depends entirely on the data types and the data value ranges, if you want to do accurate testing then those will have to be respected.
😎
Post the DDL (create table) script and as much information as possible about the four column, someone is bound to pitch in with suggestions.