Guarantee unique value in database

Question

I have a registration table in my database with a field RegistrationNumber which needs to be unique.

I am wondering about what is the best way to guarantee that I will provide unique number in every case.

What I do is in my Repository I save the new Registration, something like:

void IMyRepository.Repository(Registration registration)
{
    registration.RegistrationNumber = _getNewRegistrationNumber();

    dbContext.SaveChanges();
}

private string _getNewRegistrationNumber()
{
    // what to do? get last registration number and increment? it could be 
    // either integers or integers mixed with letters.
}

What I'm worried about is if two people complete the registration form at the same time, I'm afraid that before the first one arrives at dbContext.SaveChanges(); the second one will enter the _getNewRegistrationNumber() function and might get the same RegistrationNumber.

Any advise?

[EDIT] : GUID is too long

Creating a GUID might be the best or let the db assign an auto-incremented value. — ChiefTwoPencils, Jul 31 '17 at 23:46
https://www.bing.com/search?q=Guarantee+unique+value+in+database... there is small chance someone already tried to implement such thing... maybe even created DB feature like Unique Key or autoincrement fields... — Alexei Levenkov, Jul 31 '17 at 23:46
@ChiefTwoPencils I like the idea but I want it to be a string value because all registrationNumbers should be of same length. — joks, Jul 31 '17 at 23:49
Yes sorry, should have mentioned that Guid is too long. I have edited the post. — joks, Jul 31 '17 at 23:55

score 3 · Accepted Answer · answered Jul 31 '17 at 23:48

3

You have 3 options:

Use an computed Identiy column (Auto Increment) that will automaticly be created, if you insert a new record. The drawback is, you need a rountrip to the database, before you have an Number
Use a Guid
Use a unqiue index in your database and compute your own number. If the number already exist on save, you can catch a Exception.

answered Jul 31 '17 at 23:48

Christian Gollhardt

16,510
17
74
111

I like the first option but since I want it to be a string value, all of same length I don't think that would work. I should have mentioned that GUID is too long. I guess I will go with third option if I don't get better ideas, I thought of that but was wondering if I had better options – joks Jul 31 '17 at 23:53
Why does it need to be a string @joks? If you like the first one, just treat it as int and in your Application you can use `registration.RegistrationNumber.ToString()` – Christian Gollhardt Jul 31 '17 at 23:54
In the database I would like the field to be able to have preceding zeros (probably should've stated that in the question :( ). Because they all should have same length, so if it's int, first we have 1, and soon we will have 10, not same length. So I would like to have 00000001 and 00000010 and so forth. – joks Aug 01 '17 at 00:01
1

You know, that a string consumes much more space? This would be again a thing for your Client Application. See [`registration.RegistrationNumber.ToString("D8")`](https://stackoverflow.com/questions/4325267/c-sharp-convert-int-to-string-with-padding-zeros) @joks – Christian Gollhardt Aug 01 '17 at 00:03
1

When you say it like that I absolutely agree. Thanks a lot for opening my eyes. – joks Aug 01 '17 at 00:05

score 0 · Answer 2 · answered Jul 31 '17 at 23:48

0

The best way to guarant unique value is GUID:

void IMyRepository.Repository(Registration registration)
{
    registration.RegistrationNumber = Guid.NewGuid().ToString();

    dbContext.SaveChanges();
}

answered Jul 31 '17 at 23:48

Yurii N.

5,455
12
42
66

Guarantee unique value in database

2 Answers2