0

Does SQL Server have a mode where it pulls all PK values on a create from a single counter so no two records, across all tables, will have the same PK?

I ask for testing purposes. I had a bug where I was referencing the wrong FK property in a record. And the unit test passed because for the record in the unit test the two FKs in that record correctly had the same value.

If there was a mode to not allow any duplicate PK values across all tables, that unit test would have found the error.

David Thielen
  • 28,723
  • 34
  • 119
  • 193
  • Does this (not SQL server specific) answer your question? [Enforce/check uniqueness of primary key across multiple tables](https://stackoverflow.com/questions/9274145/enforce-check-uniqueness-of-primary-key-across-multiple-tables) – Filburt Jul 08 '23 at 19:12
  • 1
    if you testing with tables with different structure than in production, it might be a bad idea – siggemannen Jul 08 '23 at 21:18
  • 1
    Changing your PK/FK design just to make some unit tests work seems a bit extreme... pretty sure the unit tests could be written to work correctly without a massive database change. – Dale K Jul 09 '23 at 04:47
  • 1
    @DaleK My idea is not to make a change so unit tests work, it's to make a change where unit tests are more likely to flag an issue. Back in my C++ days in debug mode I would have it fill all malloc'ed & free'ed memory with a -1, not to use -1 but to make using memory I had not filled more likely to blow up spectacularly. – David Thielen Jul 09 '23 at 15:30

2 Answers2

6

The short answer is, there is no switch to flip or 'mode' that does this.

SQL Server has some built in tools to help with this, but many solutions like this are actually implemented application side. That being said, there might be a couple of ways to accomplish something that might work for this.

Sequence

Assuming that you are using some sort of numeric type for these keys, SQL Server provides a SEQUENCE object (CREATE SEQUENCE) that can help with providing always incrementing values to multiple other processes (or objects). Think of it like an IDENTITY object that is not table specific. Per Microsoft,

A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created.

You are able to add a DEFAULT contsraint to a column and assign from a sequence (ex: SO: Sequence as default value for a column). So if it was acceptable for your testing, you could add default contraints to all key columns and assign all key values from the same sequence. Personally, I would not recommend that as a production ready solution (I have actually never seen this setup in production), but it does sound like what you are looking for.

uniqueidentifier

Alternatively, if you are using some sort of string type for keys, I have seen many implementations of databases that utilizes the uniqueidentifier, which is basically just a GUID value, type for every single key value in a database. They are of course subject to the same uniqueness as other GUID types in the Microsoft sphere, which you can read about in many places, but would likely resolve the uniqueness issue you face.

You can acquire new GUIDs values from built in functions like NEWID and you can set column default values for this as well.

This as more of a production ready approach, and I have seen it implemented in many databases. I don't love this option due to additional storage required (when compared to basic int types) and GUID values are a bit harder to work with on a daily basis. But Microsoft provided this type almost specifically for this purpose, and has some decent functionality around using the values across tables.

trenton-ftw
  • 950
  • 5
  • 14
2

RDMS schema doesn't provide a way to validate data in the way you intend - you need to shape your data to guard your application in the scenario you describe.

A simple, built-in way not mentioned in the related answer would be to set a specific seed value for each IDENTITY column.

CREATE TABLE [table_a]
   (
      a_id bigint IDENTITY (1000000000000000, 1) NOT NULL
      , CONSTRAINT [PK_table_a_id] PRIMARY KEY NONCLUSTERED (a_id)
   )

CREATE TABLE [table_b]
   (
      b_id bigint IDENTITY (2000000000000000, 1) NOT NULL
      , CONSTRAINT [PK_table_b_id] PRIMARY KEY NONCLUSTERED (b_id)
   )

Using bigint data type will give you more than 9.000 unique seed values with the range from my example.

Filburt
  • 17,626
  • 12
  • 64
  • 115