April 27, 2015 at 8:24 am
This is a single table problem with two columns and 5 data integrity rules.
1.) The First Column is the primary key, Ticket, of type Integer
2.) The Second is Void of type integer also uniquely indexed excluding nulls.
3.) A void ticket must exist in the primary key and is enforced by a FK constraint.
4.) A void ticket must not be replaced by itself, enforced by table constraint
5.) A Replacement Ticket must not be void at the time of insert/update
I can handle the first four constraints, but not the fifth. and I have just developed a recursive query using CTE to move through the linked columns, but without the 5th constraint it is possible to create an endless loop, which would not be good. Anybody know a trick the will help in SQL 2005
April 27, 2015 at 8:42 am
svanduffelen 83303 (4/27/2015)
This is a single table problem with two columns and 5 data integrity rules.1.) The First Column is the primary key, Ticket, of type Integer
2.) The Second is Void of type integer also uniquely indexed excluding nulls.
3.) A void ticket must exist in the primary key and is enforced by a FK constraint.
4.) A void ticket must not be replaced by itself, enforced by table constraint
5.) A Replacement Ticket must not be void at the time of insert/update
I can handle the first four constraints, but not the fifth. and I have just developed a recursive query using CTE to move through the linked columns, but without the 5th constraint it is possible to create an endless loop, which would not be good. Anybody know a trick the will help in SQL 2005
Can you explain what this means, in terms of column values in the database, please:
A Replacement Ticket must not be void at the time of insert/update
April 27, 2015 at 8:52 am
[Ticket] [Void]
[0] Null
[1] [0]
[2] [1]
Ticket [0] is replaced by Ticket [1], Ticket [1] is replaced by Ticket [2] and so on.
however ticket [0] must not be allowed to replace ticket [2],[1] or itself.
April 27, 2015 at 9:03 am
If you create a constraint that void < ticket, does that help? (Assuming ticket is an identity column.)
April 27, 2015 at 9:15 am
The tickets are unique but they may not be sequential or forever increasing. So a constraint with a sub-query is necessary, but I have no idea how to do that.
April 27, 2015 at 9:45 am
I need to put
NOT EXISTS(SELECT * FROM [TICKETS] WHERE [VOID] = [TICKET])
Into a constraint. So that the chained tickets do not form a loop.
April 27, 2015 at 10:09 am
svanduffelen 83303 (4/27/2015)
I need to putNOT EXISTS(SELECT * FROM [TICKETS] WHERE [VOID] = [TICKET])
Into a constraint. So that the chained tickets do not form a loop.
This needs to be refined. The third row of your sample data (2,1) would violate it.
April 27, 2015 at 11:40 am
The Existence check would only return False after the update. At the time of update the check would return true. As [1] will not have been saved the table yet.
April 27, 2015 at 11:56 am
You are right, it does need another constraint.
Update and Delete Action only available to null valued voids.
April 27, 2015 at 1:27 pm
While the 'does not exist constraint solution' can be accomplished by producing a UDF for the sub query in a constraint.
The recursive query's infinite loop can be solved by ensuring that the CTE object does not contain duplicate tickets.
As the second solution is simpler and allows more latitude for users to fix their own mistakes. I am taking that approach.
Thank-you for your help
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy