I have a lookup table that implements a system-generated Id for DRI purposes. The lookup table has columns defining the effective and obsolete dates of the record i.e. the period during which a lookup term is valid for business use. Normally I would create a unique index on the lookup term to ensure that it is not duplicated. However, the business wishes to reserve the right to be able to reuse a previously used, now obsolete, term - because there are only so many good words to go around they may want to reuse a previously obsoleted term but give it a slightly different meaning (If they wish to start using the same term with the same meaning again they'd just remove the obsolete date). In this scenario old records, using the obsolete term, would still need to reference the now-obsolete version while newly created records would reference the new instance of the term
I thought I could simply modify my unique index on the lookup term to include the obsolete date but this doesn't produce the desired result. Note that I have the same requirement for the Business_Code column.
Is there a straight-forward way of doing what my customer is asking?
Here's my lookup table DDL. This will eventually be turned into a SP that I would use to generate all reference tables with the same spec.. The "Reference_Table" term will be replaced by a variable that I will pass to the SP.
DROP TABLE IF EXISTS [dbo].[R_Reference_Table]
CREATE TABLE [dbo].[R_Reference_Table] (
[Reference_Table_Id] INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PKC_Reference_Table] PRIMARY KEY CLUSTERED
,[Reference_Table_Name] VARCHAR(255) NOT NULL
,[Reference_Table_Description] VARCHAR(MAX) NULL
,[Reference_Table_Business_Code] VARCHAR(12) NULL
,[Reference_Table_Effective_Date] DATE NOT NULL CONSTRAINT [DF_Reference_Table_Effective_Date] DEFAULT GETDATE()
,[Reference_Table_Obsolete_Date] DATE NULL
,[Reference_Table_Created_On] DATETIME NOT NULL CONSTRAINT [DF_Reference_Table_Created_On] DEFAULT GETDATE()
,[Reference_Table_Updated_On] DATETIME NOT NULL CONSTRAINT [DF_Reference_Table_Updated_On] DEFAULT GETDATE()
,[Reference_Table_Updated_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_Reference_Table_Updated_By] DEFAULT [dbo].[Get_User_Name]()
,[Reference_Table_RowVersion] ROWVERSION NOT NULL
,[Reference_Table_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_Reference_Table_GUID] DEFAULT NEWID()
,CONSTRAINT [CK_Obsolete_Date_GE_Effective_Date] CHECK ([Reference_Table_Obsolete_Date] >= [Reference_Table_Effective_Date])
,CONSTRAINT [UIX_Unique_Effective_Reference_Table_Name] UNIQUE ([Reference_Table_Name], [Reference_Table_Obsolete_Date])
,CONSTRAINT [UIX_Unique_Effective_Reference_Table_Business_Code] UNIQUE ([Reference_Table_Business_Code], [Reference_Table_Obsolete_Date])
)
GO
CREATE TRIGGER [dbo].[R_Reference_Table_On_Update] ON [dbo].[R_Reference_Table] FOR UPDATE AS
SET NOCOUNT ON
UPDATE TBL SET
TBL.[Reference_Table_Updated_On] = GETDATE()
,TBL.[Reference_Table_Updated_By] = [dbo].[Get_User_Name]()
FROM [dbo].[R_Reference_Table] TBL
JOIN inserted INS
ON TBL.[Reference_Table_Id] = INS.[Reference_Table_Id]
GO
Thanks in advance for any advice you can provide.
October 26, 2020 at 3:07 pm
Can you expand on what you mean by "but this doesn't produce the desired result"?
Note also that a unique constraint is not quite the same as a unique index.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 26, 2020 at 3:22 pm
Make sure the [Reference_Table_Obsolete_Date] is getting populated correctly on all obsoleted rows.
If that date is NULL for both rows (old and new), that will not be allowed for a unique index (a NULL key value can only appear once in a unique index key; i.e., (1, NULL), (1, NULL) would not be allowed in a unique index).
Also, if it's possible that you would obsolete a row(s) more than once in a calendar day, you should use datetime for the obsolete "date" value.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 26, 2020 at 5:13 pm
Phil. Thanks for the reply.
Now I'm 2nd guessing myself about the results I was seeing. I think it was actually working although the error message was not what I was expecting. The error message is;
Violation of UNIQUE KEY constraint 'UIX_Unique_Effective_Reference_Business_Code'. Cannot insert duplicate key in object 'dbo.R_Reference_Table'. The duplicate key value is (<NULL>, <NULL>).
I don't know why it is saying "The duplicate key value is (<NULL>, <NULL>)". I expected "...The duplicate key value is ("Test", <NULL>)"
You did highlight to me a distinction between a unique constraint and a unique index. When I converted the constraints to unique indexes I got the result I was expecting.
Cannot insert duplicate key row in object 'dbo.R_Reference_Table' with unique index 'UIX_Unique_Effective_Reference_Table_Name'. The duplicate key value is (TEST, <NULL>).
I now think that either approach would meet my needs.
October 26, 2020 at 5:20 pm
Scott. Thanks for the reply.
My reply to Phil gives some more background. Obsolete dates are being captured correctly. As far as I know, the Date datatype is sufficient for Effective and Obsolete dates. Creating and obsoleting lookup values is rare in my case. It usually coincides with some significant business process change that takes a long time to implement.
October 27, 2020 at 12:34 pm
To future readers;
The responses from Phil and Scott helped me solve my problem. Ultimately, for my use case, I used unique constraints as is specified in my DDL above. Further testing confirmed that I was mistaken (I misinterpreted) by the result I was getting and that the unique constraints were indeed working as desired.
I made that decision based on the very good explanation of the distinction between unique constraints and unique indexes found here. My choice was based on the fact that in my case I'm not using the index in any code/functional sense but rather simply to enforce a uniqueness constraint.
Thanks again to Phil and Scott.
Finally; in the interest of giving back to the community. If you are interested in how I turned my table spec into a stored procedure - here it is;
I use this in my database development to accelerate the process of creating reference tables. I won't belabor the rationale for my choices of column attributes nor the trigger but if you're interested, just ping me.
I'm not a trained, professional database developer. I do it because there's a need for it where I work. This code works for me but I'm sure that there are more skilled developers who could point out flaws in what I've done or recommend improvements. I'd welcome that.
-----------------------------------------------------------------------------------------------------------------------
-- PROCEDURE [Create_Ref_Table]
-- this procedure can be used to create a reference table with a consistent specification.
-- USAGE; EXECUTE [dbo].[Create_Ref_Table] 'TABLENAME'
-- where 'TABLENAME' is the name of the table to be created. Note that the procedure prepends the name with an 'R_'
-- to denote that it is a reference table.
-----------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS [dbo].[Create_Ref_Table]
GO
CREATE PROCEDURE [dbo].[Create_Ref_Table] @TableName AS VARCHAR(255) AS
BEGIN
DECLARE @SQL_String AS NVARCHAR(MAX)
SET @SQL_String =
'DROP TABLE IF EXISTS [dbo].[R_' + @TableName + '];
CREATE TABLE [dbo].[R_' + @TableName + '] (
[' + @TableName + '_Id] INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PKC_' + @TableName + '] PRIMARY KEY CLUSTERED
,[' + @TableName + '_Name] VARCHAR(255) NOT NULL
,[' + @TableName + '_Description] VARCHAR(MAX) NULL
,[' + @TableName + '_Business_Code] VARCHAR(12) NULL
,[' + @TableName + '_Sort_Order] NUMERIC NULL
,[' + @TableName + '_Effective_Date] DATE NOT NULL CONSTRAINT [DF_' + @TableName + '_Effective_Date] DEFAULT GETDATE()
,[' + @TableName + '_Obsolete_Date] DATE NULL
,[' + @TableName + '_Created_On] DATETIME NOT NULL CONSTRAINT [DF_' + @TableName + '_Created_On] DEFAULT GETDATE()
,[' + @TableName + '_Updated_On] DATETIME NOT NULL CONSTRAINT [DF_' + @TableName + '_Updated_On] DEFAULT GETDATE()
,[' + @TableName + '_Updated_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_' + @TableName + '_Updated_By] DEFAULT [dbo].[Get_User_Name]()
,[' + @TableName + '_RowVersion] ROWVERSION NOT NULL
,[' + @TableName + '_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_' + @TableName + '_GUID] DEFAULT NEWID()
,CONSTRAINT [CK_' + @TableName + 'Obsolete_Date_GE_Effective_Date] CHECK ([' + @TableName + '_Obsolete_Date] >= [' + @TableName + '_Effective_Date])
,CONSTRAINT [UIX_Unique_Effective_' + @TableName + '_Name] UNIQUE ([' + @TableName + '_Name], [' + @TableName + '_Obsolete_Date])
,CONSTRAINT [UIX_Unique_Effective_' + @TableName + '_Business_Code] UNIQUE ([' + @TableName + '_Business_Code], [' + @TableName + '_Obsolete_Date])
);
'
EXECUTE sp_executesql @SQL_String
SET @SQL_String =
'CREATE TRIGGER [dbo].[R_' + @TableName + '_On_Update] ON [dbo].[R_' + @TableName + '] FOR UPDATE AS
SET NOCOUNT ON
UPDATE TBL SET
TBL.[' + @TableName + '_Updated_On] = GETDATE()
,TBL.[' + @TableName + '_Updated_By] = [dbo].[Get_User_Name]()
FROM [dbo].[R_' + @TableName + '] TBL
JOIN inserted INS
ON TBL.[' + @TableName + '_Id] = INS.[' + @TableName + '_Id]
'
EXECUTE sp_executesql @SQL_String
END
GO
October 27, 2020 at 7:01 pm
Since these are reference tables (e.g. lookup, dictionary) - I would standardize the column names. No need to put the reference type as part of the column name. This would make it easier to build dynamic code to insert/update the tables as well as standardize code using the reference tables.
One option would be to use 'Entry' in the column name. That would get you:
CREATE TABLE [dbo].[R_' + @TableName + '] (
[Entry_Id] INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PKC_' + @TableName + '] PRIMARY KEY CLUSTERED
,[Entry_Name] VARCHAR(255) NOT NULL
,[Entry_Description] VARCHAR(MAX) NULL
,[Entry_Business_Code] VARCHAR(12) NULL
,[Entry_Sort_Order] NUMERIC NULL
,[Entry_Effective_Date] DATE NOT NULL CONSTRAINT [DF_' + @TableName + '_Effective_Date] DEFAULT GETDATE()
,[Entry_Obsolete_Date] DATE NULL
,[Entry_Created_On] DATETIME NOT NULL CONSTRAINT [DF_' + @TableName + '_Created_On] DEFAULT GETDATE()
,[Entry_Updated_On] DATETIME NOT NULL CONSTRAINT [DF_' + @TableName + '_Updated_On] DEFAULT GETDATE()
,[Entry_Updated_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_' + @TableName + '_Updated_By] DEFAULT [dbo].[Get_User_Name]()
,[Entry_RowVersion] ROWVERSION NOT NULL
,[Entry_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_' + @TableName + '_GUID] DEFAULT NEWID()
,CONSTRAINT [CK_' + @TableName + 'Obsolete_Date_GE_Effective_Date] CHECK ([Entry_Obsolete_Date] >= [Entry_Effective_Date])
,CONSTRAINT [UIX_Unique_Effective_' + @TableName + '_Name] UNIQUE ([Entry_Name], [Entry_Obsolete_Date])
,CONSTRAINT [UIX_Unique_Effective_' + @TableName + '_Business_Code] UNIQUE ([Entry_Business_Code], [Entry_Obsolete_Date])
);
With that - any code using a reference table can get the Entry_Name, Entry_Description, etc...
The second thing I would do is set these up as temporal tables instead of using a trigger. Using a temporal table will set the tables up with a history table so you can see the values of the data before and after they have been updated, and track when they were updated/created or set to obsolete, made effective, etc...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 27, 2020 at 8:23 pm
Jeffrey;
Thanks for the suggestion. This is definitely a sound approach for the reasons you've noted. The reason I've created it as I have is for the convenience/clarity of writing quick queries that join several reference tables to a base table e.g.
SELECT
*
FROM [BASE TABLE] T1
JOIN [R TABLE 1] R1
ON R1.KEY = T1.R1_FK
JOIN [R TABLE 2] R2
ON R2.KEY = T1.R2_FK
JOIN [R TABLE 3] R3
ON R3.KEY = T1.R3_FK
...
Which actually works fine as a query with tables having columns named as you've defined. The query result will have multiple columns with the same name. That's the part the vexes me because when you try to persist that query as a temp table it throws an error because you can't create a table with multiple columns that have the same name. This means that you have to include all of the columns from the reference tables and alias the column names so you can dump the query to a temp table. I'm just pre-aliaising them 😉
With regard to temporal tables - they're awesome and when I'm working with a server that supports them I absolutely use them. Sadly the server I'm working with right now is 2014.
October 27, 2020 at 9:00 pm
Well - you should not be using '*' in your queries, as you noted you cannot do that and dump everything into a temp table. You wouldn't want to do that anyways...as that just creates a temp table that is much larger than is needed.
A good intellisense utility makes it trivial to add specific columns and alias them - even Microsoft's intellisense (which I do not use) provides a drop down list of available columns based on the table alias. You could even build a code snippet...something that can be copied/pasted and then change the alias (this could be done using a template - and template parameters).
The utility I utilize has the ability to create snippets and templates - and have a lot of those kinds of things auto-filled.
As for you working on 2014 - sorry, you posted in a 2016 forum so I just assumed you were working with that version.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 28, 2020 at 8:52 am
This coding:
'CREATE TABLE [dbo].[R_' + @TableName + '] '
Is not bullet proof.
You may never use brackets in object names, but some people do. It's a quite effective method to keep cowboys at bay.
Proper way to do dynamic SQL is this:
'CREATE TABLE [dbo].' + quotename('R_' + @TableName)
_____________
Code for TallyGenerator
October 28, 2020 at 2:16 pm
Sergiy;
Thanks for sharing. I confess that I'm not certain I understand the purpose of coding as you've described. What problem does it solve? Is it a method of mitigating risks associated with dynamic SQL (I've read that there are risks, and in principle I think I get it although I've never seen instances of it)? If so, what risk would it be addressing?
In my case, my SP is only a utility for my use in the creation of a DB. It won't be part of the final product (although I don't think that's a reason to not use high-quality code).
I'm asking out of ignorance since, while I have the knowledge/skills to "get the job done", (and know where to come when I don't - Yay SQL Server Central!) I know that there's still a lot I don't know about high performance, high quality, rigorous SQL coding.
October 28, 2020 at 6:48 pm
the problem will appear when someone creates a table named “Year[2020]”. Then your code will generate invalid SQL statements. My version handles this kind of names correctly.
_____________
Code for TallyGenerator
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply