Often in database design a table has a secondary unique key which defines a random alpha-numeric value; lets call this Code. While the table’s primary key is used for foreign key references in the database, the Code column has several advantages and uses. Since it is not a sequential value it can be shown to the user without giving away possible valuable information; for example giving away your sales volume. It can also be used as a URL query parameter to discourage one-up-attacks (note this should not be a replacement for proper data security).
So now that we are sold on having a Code column, what is the best way to implement it? Here is a method you can use to implement a proper Code column and have the database generate the value for you. This is a similar design pattern to using an IDENTITY column to generate the next value for a primary key. We would like for the column to have a default value of a new Code value, such that the application tier doesn’t have to worry about setting the value. An issue with this in SQL Server is that column default values do not allow for ‘dynamic’ values; we can use a view to get around this. As for the Code generation algorithm I piggybacked on the NEWID() function which generates a new uniqueidentifier. There are countless other algorithms you could use for this.
Create a view to generate the new codes…
CREATE VIEW [dbo].[Codes]
AS
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), ‘-’, ”), 0, 16)) AS Code;
Create a function which returns a new code…
CREATE FUNCTION [dbo].[NewCode]
( )
RETURNS NVARCHAR (50)
AS
BEGIN
RETURN (SELECT Code FROM Codes)
END
Now we can use the dbo.NewCode() function as the default value for our table’s Code column..
ALTER TABLE [dbo].[XXX] ADD CONSTRAINT [DF_XXX_Code] DEFAULT ([dbo].[NewCode]()) FOR [Code]
That’s it! Now if you insert a row into the table the Code column will have a new value in it by default. Simple.
UPDATE
Here is a much better algorithm to generate codes. The previous algorithm has a ‘hidden’ issue; the NEWID() function only generates the letters A-H, so you don’t get the security of the full 26 character alphabet. The algorithm below also lets you choose which characters you want. This allows you to prevent issues like 1 and I, and 0 and O confusion.
CREATE VIEW [dbo].[Codes]
AS
select
[Code] =
substring(ch, convert(int, RAND() * (LEN(ch) – 1)) + 1, 1) +
substring(ch, convert(int, RAND() * (LEN(ch) – 1)) + 1, 1) +
substring(ch, convert(int, RAND() * (LEN(ch) – 1)) + 1, 1) +
substring(ch, convert(int, RAND() * (LEN(ch) – 1)) + 1, 1) +
substring(ch, convert(int, RAND() * (LEN(ch) – 1)) + 1, 1) +
substring(ch, convert(int, RAND() * (LEN(ch) – 1)) + 1, 1) +
substring(ch, convert(int, RAND() * (LEN(ch) – 1)) + 1, 1) +
substring(ch, convert(int, RAND() * (LEN(ch) – 1)) + 1, 1)
from
(select ch = ‘ABCDEFGHJKLMNPQURSUVWXYZ23456789′) a