Self-Joining Check constraint ?

[Exie]

Hi Folks,

I have a table with orders in it, I'd like to have a constraint so that order lines MUST have a order header record. eg.

ID LINE REF
6 0 MYORDER101
6 1 MYORDER101
6 2 MYORDER101
7 1 MYORDER102
7 2 MYORDER102
7 3 MYORDER102

In this...
Order No. 6 would pass, as line 0 is the order header.
Order No. 7 however should fail as it has no Line 0.

Any tips on how I could do this ?

 

[SantaMufasa]

Exie,

CHECK CONSTRAINTS cannot look at data on other rows in the table...only on the same row for which the current check constraint is checking.

To do what you want (i.e., checking other rows in the same table), you use a DATABASE TRIGGER.

Let us know if you need help on that after you have looked at the documentation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

However, if you try to use a row level database trigger to check other rows in the same table, you will fall foul of mutating table rules. It would probably work with a statement level trigger.