× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.