Re: SQL DDL and field reference file.

[CRPence <CRPbottle@xxxxxxxxx>]

rob@xxxxxxxxx wrote:
> <<SNIP>>
>
> I can create a table that has constraints but if I type in
> CREATE TABLE BS (SOMECOL CHAR (1),
>   CHECK (SOMECOL<>ColumnNotInTable))
> it will tell me to get bent.  So if the CREATE TABLE can be
> reasonable on this, why can't it be reasonable on the field
> reference concept?  I don't buy that it can't.

  "Can't" and "reasonableness", are not equivalent.  Given the 
request CREATE TABLE BS (C1 CHAR ,C2 CHAR, CHECK (C1>C2), UNIQUE 
(C2), PRIMARY (C1,C2)), how reasonable would it be to receive 
"column C2 not found; table not created or constraint not added" in 
response to the request to CREATE TABLE DI AS SELECT C1 FROM BS 
which as statement source, makes no mention of a column C2?  Is it 
also reasonable for the DB2 to provide some inference of what is 
desired to be applied to the data, beyond just choosing the columns 
for that data, when the constraints on the data are probably better 
decided explicitly by the designer & creator of the database? 
Consider also, that the above are *very* simplified.

  It is moot anyway, what is provided in the DB2 for i.  Whatever 
is available in the standards, is that which is relevant.  So 
consider that the CREATE TABLE AS intends to create a TABLE based on 
a SELECT *query* of the database.  A query itself, does not contain 
the constraint definitions.

  So even supposing such a function would be enabled by 
[additional] INCLUDING clauses for the constraint details like is 
done for various column attributes, consider that requires going 
back to each TABLE, perhaps down through a VIEW to a column which is 
not even the same as what is in the VIEW.  For example, the selected 
column from the query to create the table may be a CHAR which is the 
first byte of a four-byte numeric column which has a CHECK 
constraint defined as FBNC BETWEEN (1 AND 31).  Does the database 
have to define the constraint on the new column as NEWCOLUMN BETWEEN 
'1' AND '3', ignore the constraint entirely, try to apply the 
constraint to column FBNC which is not part of the new TABLE, or 
what?  That of course does not even address the conundrum of joins 
in the select query, nor JOIN or UNION in the VIEWs named in select 
query of a CREATE TABLE AS.

  In any case, the databases would no longer be looking at just the 
[column definitions of the] query from which it builds the TABLE, 
but drilling down\back to each TABLE referenced by the query, for 
what possible constraint information was originally defined.  I 
believe it really makes little sense.

Regards, Chuck