Does Derby support ASSERTION?

[Sisilla Sookdeo <si...@paramounttransport.com>]

Hello All,

 

I tried executing the following SQL code in NetBeans IDE:

 

CREATE ASSERTION PartialRetiringEquipment  

CHECK 

(   

(SELECT COUNT (R.PTTEquipmentID) FROM RetiredEquipment R) < =   

(SELECT COUNT (All.PTTEquipmentID) FROM Equipment All)

 )

 

I received the following error message:

An error has occurred during execution: Syntax error: Encountered
"ASSERTION" at line 1, column 8

 

I am using Derby 10.2.1.6 on Windows XP Professional. I appreciate any
help.

 

Thanks,

Sisilla

Re: Does Derby support ASSERTION?

[Kristian Waagan <Kr...@Sun.COM>]

Sisilla Sookdeo wrote:
> Hello All,
> 
>  
> 
> I tried executing the following SQL code in NetBeans IDE:
> 
>  
> 
> CREATE ASSERTION PartialRetiringEquipment 
> 
> CHECK
> 
> (  
> 
> (SELECT COUNT (R.PTTEquipmentID) FROM RetiredEquipment R) < =  
> 
> (SELECT COUNT (All.PTTEquipmentID) FROM Equipment All)
> 
>  )
> 
>  
> 
> I received the following error message:
> 
> An error has occurred during execution: Syntax error: Encountered 
> “ASSERTION” at line 1, column 8
> 
>  
> 
> I am using Derby 10.2.1.6 on Windows XP Professional. I appreciate any help.

Hello Sisilla,

According to http://wiki.apache.org/db-derby/SQLvsDerbyFeatures , Derby 
does not support SQL assertions.
If you think this feature should make it into Derby, feel free to log a 
new feature request at http://issues.apache.org/jira/browse/DERBY

The only work-around I can think of at the moment, is to use a set of 
triggers (maybe in combination with a check constraint in a helper 
table), but this would not be very pretty...
(would have to try it out to make sure it works as well!)



regards,
-- 
Kristian

> 
>  
> 
> Thanks,
> 
> Sisilla
>