Re: Question on Structuring Product Attributes
Date: Fri, 08 Feb 2013 20:29:42 +0100
Message-ID: <kf3jm7$gdh$1_at_dont-email.me>
On 2013-02-08 10:36, derek.asirvadem_at_gmail.com wrote:
> On Friday, 8 February 2013 16:53:26 UTC+11, Lennart Jonsson wrote:
>> On 2013-02-04 02:01, derek.a..._at_gmail.com wrote: >> >> I tried to read through this thread, so there might be peaces that I >> missed. I like the idea that you present, but AFAIK DB2 (LUW) does not >> support sub queries in check constraints.
>
> Someone else may have said that, but I did not (I don't read or reply all the posts, for reasons that are probably obvious). Also, there are a few non-technical types here who keep on misunderstanding my posts, and keep on posting solutions that do not work, instead of trying to understand my posts, but their posts look like they are trying to understand ... and in those exchanges I gave up trying to correct them.
>
> Neither DB2 nor Sybase allow subqueries in CHECK constraint definitions, and my posted code Sybase does not contain that. However that is exactly what I want, and I gave the method to overcome that "limitation".
>
> The method uses a Function (UDF) which contains the SELECT (which is a straight query, not a subquery) and returns an INT true/false. The CHECK Constraint was a constraint-with-search-condition, which called the Function as an expression (exactly the same as the posted Sybase code, with syntax differences of course). In DB2, the constraint defn is separate (in Sybase it is part of the CREATE/ALTER TABLE). In DB2, the CREATE TABLE named the Check Constraint at the table, not column level.
>
I can only speculate on why LUW treats it as a sub select, but LUW
pretty much inlines functions and triggers into the query that is
evaluated. A query like:
insert into ... values ( ... )
can be thought of as:
insert into ... select * from (values ( ... ))
where TRUE
<=>
insert into ... select * from (values ( ... ))
where ValidateExclusive_fn (ProductId, 'B') = 1
<=>
insert into ... select * from (values ( ... ))
where
(select COALESCE(1, 0)
from Product
where ProductId = x.ProductId
and ProductType = x.ProductType) = 1
BTW, is the COALESCE necessary? Either we have a row with tuple(1) or we don't have a row at all?
Cheers
/Lennart
[...] Received on Fri Feb 08 2013 - 20:29:42 CET