November 19, 2015 at 10:26 am
drew.allen (11/19/2015)
It violates Second Normal Form in that the foreign key is NOT dependent on the whole of the primary key. The fact that you had to specify IGNORE_DUP_KEY in order to create a "unique" constraint is further evidence that you are violating Second Normal Form.
Drew
The 2nd Normal form is not being violated at all. Every column on that able (TableA) is either part of the primary key or an attribute of that key in it's entirety.
The need for the index (which is not acting as a constraint on TableA) it's a work around (a kludge if you will) to bypass a platform (MS SQL Server) specific limitation. That limitation being that SQL Server doesn't allow the creation of CHECK constraints that reference other tables.
That said, I do agree that this is a bit of an odd-ball circumstance and that if I were reading this with an admittedly limited context, it would raise a red flag and or set off the "weird smell alarm".
That said, both tables (A & B) are normalized to the 4th normal form, it's the relationship between the two that's a little awkward...
November 19, 2015 at 10:26 am
I actually tried to create a test on SQL 2012 (don't have SQL 2014 available) and when I tried to create the unique constraint, it failed saying that it had found a duplicate key. Since you said you were able to create the unique constraint, it looks like your current table may currently be unique for those two fields--and therefore that the effective date may not really be part of the primary key.
The bigger issue, is that IGNORE_DUP_KEY doesn't do what you seem to think it will do. SQL will still enforce that the key is unique, it will simply ignore any errors raised by trying to insert a duplicate key. Try the following.
CREATE TABLE TEST (
GroupID INT,
PayorID INT,
Effective_Date DATE
)
INSERT TEST(GroupID, PayorID, Effective_Date)
VALUES( 1, 1, '2015-10-01')
SELECT *
FROM TEST
CREATE UNIQUE NONCLUSTERED INDEX IX_ignore_dups
ON TEST( GroupID, PayorID )
WITH (IGNORE_DUP_KEY = ON)
INSERT TEST(GroupID, PayorID, Effective_Date)
VALUES( 1, 1, '2015-11-01')
SELECT *
FROM TEST
DROP TABLE TEST
Unless things have changed in SQL 2014, it will simply discard the record with the duplicate "unique" key.
Again, seeing that we're only aware of the three "key" fields, it's very difficult to determine whether the problem is in specifying the wrong primary key or in the normalization.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 19, 2015 at 10:31 am
drew.allen (11/19/2015)
I actually tried to create a test on SQL 2012 (don't have SQL 2014 available) and when I tried to create the unique constraint, it failed saying that it had found a duplicate key. Since you said you were able to create the unique constraint, it looks like your current table may currently be unique for those two fields--and therefore that the effective date may not really be part of the primary key.The bigger issue, is that IGNORE_DUP_KEY doesn't do what you seem to think it will do. SQL will still enforce that the key is unique, it will simply ignore any errors raised by trying to insert a duplicate key. Try the following.
CREATE TABLE TEST (
GroupID INT,
PayorID INT,
Effective_Date DATE
)
INSERT TEST(GroupID, PayorID, Effective_Date)
VALUES( 1, 1, '2015-10-01')
SELECT *
FROM TEST
CREATE UNIQUE NONCLUSTERED INDEX IX_ignore_dups
ON TEST( GroupID, PayorID )
WITH (IGNORE_DUP_KEY = ON)
INSERT TEST(GroupID, PayorID, Effective_Date)
VALUES( 1, 1, '2015-11-01')
SELECT *
FROM TEST
DROP TABLE TEST
Unless things have changed in SQL 2014, it will simply discard the record with the duplicate "unique" key.
Again, seeing that we're only aware of the three "key" fields, it's very difficult to determine whether the problem is in specifying the wrong primary key or in the normalization.
Drew
2014 behaviour has not changed, it is as you describe.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 19, 2015 at 10:40 am
RonKyle (11/19/2015)
It violates Second Normal Form in that the foreign key is NOT dependent on the whole of the primary key. The fact that you had to specify IGNORE_DUP_KEY in order to create a "unique" constraint is further evidence that you are violating Second Normal Form.
It's been a few years since I've studied the normal forms. But I used to know them inside and out and I don't think they apply to anything outside their table. I do agree that something doesn't seem right. But not enough information is known to us.
Is this an OLAP table? It reminds me of a 2SCD dimension table. But as with all dimension tables, it has an auto integer key.
No this is for a transaction database. If have some time this evening I'll post up the BRD requirements and the complete table structures.
High level... This for a table driven "rules engine", The temporal aspect of TableA relates to pay-out contracts that are enforce for given periods of time and who's specific details may change over time. TableB is an "exceptions" table. So, not every group/payor combo has an exception, but if it does, that exception applies to every version in TableB (at least until the powers-to-be change their minds and have me add the temporal component to TableB as well).
November 19, 2015 at 11:04 am
drew.allen (11/19/2015)
I actually tried to create a test on SQL 2012 (don't have SQL 2014 available) and when I tried to create the unique constraint, it failed saying that it had found a duplicate key. Since you said you were able to create the unique constraint, it looks like your current table may currently be unique for those two fields--and therefore that the effective date may not really be part of the primary key.The bigger issue, is that IGNORE_DUP_KEY doesn't do what you seem to think it will do. SQL will still enforce that the key is unique, it will simply ignore any errors raised by trying to insert a duplicate key. Try the following.
CREATE TABLE TEST (
GroupID INT,
PayorID INT,
Effective_Date DATE
)
INSERT TEST(GroupID, PayorID, Effective_Date)
VALUES( 1, 1, '2015-10-01')
SELECT *
FROM TEST
CREATE UNIQUE NONCLUSTERED INDEX IX_ignore_dups
ON TEST( GroupID, PayorID )
WITH (IGNORE_DUP_KEY = ON)
INSERT TEST(GroupID, PayorID, Effective_Date)
VALUES( 1, 1, '2015-11-01')
SELECT *
FROM TEST
DROP TABLE TEST
Unless things have changed in SQL 2014, it will simply discard the record with the duplicate "unique" key.
Again, seeing that we're only aware of the three "key" fields, it's very difficult to determine whether the problem is in specifying the wrong primary key or in the normalization.
Drew
Good catch...
The unique index worked fine with my existing test data but when I attempted to insert a new "duplicate" row, the row failed to insert...
Back to the drawling board...
November 19, 2015 at 11:20 am
A reprieve... Just had a conversation with the SVP in charge of the project...
In the end we both agreed that the specific exception can change from contract version to contract version and should have the temporal component added to it as well...
Problem, no longer a problem... 😀
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply