Must Not Exist Constraint

Question

Must Not Exist Constraint

svanduffelen 83303

Valued Member

Points: 61
More actions
April 27, 2015 at 8:24 am

#145759

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246728 More actions · Answer 1

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

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.

svanduffelen 83303 Valued Member Points: 61 More actions · Answer 2

[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.

Phil Parkin SSC Guru Points: 246728 More actions · Answer 3

If you create a constraint that void < ticket, does that help? (Assuming ticket is an identity column.)

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.

svanduffelen 83303 Valued Member Points: 61 More actions · Answer 4

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.

svanduffelen 83303 Valued Member Points: 61 More actions · Answer 5

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.

Phil Parkin SSC Guru Points: 246728 More actions · Answer 6

svanduffelen 83303 (4/27/2015)
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.

This needs to be refined. The third row of your sample data (2,1) would violate it.

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.

svanduffelen 83303 Valued Member Points: 61 More actions · Answer 7

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.

svanduffelen 83303 Valued Member Points: 61 More actions · Answer 8

You are right, it does need another constraint.

Update and Delete Action only available to null valued voids.

svanduffelen 83303 Valued Member Points: 61 More actions · Answer 9

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