Automatically assigning a random string to new records

Question

I already have a customer table, but from now on new records have to be assigned a unique random alphanumeric string of 5 characters in a new column. Is there anyways I can do this without having to use Trigger (on insert?)

Answer 1

You could use a default constraint that calls a function that generates your random string.

Answer 2

For the trigger: Well.. I'd use a trigger It has the advantage of working with previous code.

But you could also create a stored procedure that wraps your inserts into the table.

For the unique 5 characters: You could create an md5 over your initial set and maybe time, then recode it from base 10 to base 255.

The leftovers can be truncated.

Answer 3

The default constraint calling a function would work best. However you should examine how many records you'll be creating.

While 5 characters may be more then enough to uniquely identify your intended number of records (assuming A-Z, 0-9 I believe that's about 60 million codes), when you start randomly generating them you may find that you hit the birthday paradox rather quickly.

Answer 4

I had a similar situation but was generating the string in the code then sending it to the DB. Of course this is used on a very small table so I was not to worried about speed. Thought it might help, though it assumes you are using c#.

    /// Generates a string and checks for existance
    /// <returns>Non-existant string as ID</returns>
    public static string GetRandomNumbers(int numChars, string Type)
    {
        string result = string.Empty;
        bool isUnique = false;

        while (!isUnique)
        {
            //Build the string
            result = MakeID(numChars);
            //Check if unsued
            isUnique = GetValueExists(result, Type);
        }
        return result;
    }
    /// Builds the string 
    public static string MakeID(int numChars)
    {
        string random = string.Empty;

        string[] chars = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" };
        Random rnd = new Random();
        for (int i = 0; i < numChars; i++)
        {
            random += chars[rnd.Next(0, 35)];
        }
        return random;

    }
    /// Checks database tables based on type for existance, if exists then retry
    /// <returns>true or false</returns>
    private static bool GetValueExists(string value, string Type)
    {
        bool result = false;

        string sql = "";
        if (Type == "URL")
        {
            sql = string.Format(@"IF EXISTS (SELECT COUNT(1) FROM myTable WHERE uniqueString = '{0}') 
            BEGIN 
                SELECT 1 
            END  
            ELSE  
            BEGIN 
                SELECT 0 
            END ", value);
        }
        //query the DB to see if it's in use
        result = //ExecuteSQL

        return result;
    }