--- In IB-Architect@y..., Jim Starkey <jas@n...> wrote:

> At 09:25 PM 3/24/01 -0000, dianeb77@h... wrote:
> >
> >According to the SQL standard, all constraints have names -- either
> >explicit names assigned lovingly by the user, or implicit names
> >generated by the vendor.
> >
>
> Hmmm. This sounds very committee-like -- names are required to
> drop things, we invented a syntax that doesn't include names, so
> a conforming product must invent a non-standard convention.

Actually, SQL92 syntax looks like this:

<column definition> ::= <column name> <...>
[ <column constraint definition>... ]

<column constraint definition> ::= [ <constraint name definition> ]
<column constraint>
[ <constraint attributes> ]

So, according to the syntax rules, it is up to the user to decide
whether to attempt to specify name.

However, lest you ever forget you are dealing with a committee:
according to the SQL92 conformance rules, it is up to the vendor to
decide whether to support explicit <constraint name ...> clause, since
<constraint name ...> support is not required at Entry level
conformance. Sigh.

> Or did I miss something?

Details, maybe; essence, unfortunately not.

> I believe T.S. Eliot addressed this problem, but only in cats.

[snort]

> Does the standard devulge how the vendor is to disclose the
> implicit inner names?

The SQL92 way [or should that be "Way"?] is to expose the names, as
well as dependencies and other choice bits of metadata, through the
Information Schema views (also part of SQL92). Of course, as has
probably been discussed before, support for the Information Schema is
not required for Entry level conformance, therefore rate of adoption
is, ummmm, not high ... [Fiddle dee dee, I'll think about that
tomorrow :-) ]

If supported [it would not run?], the Information Schema includes
views that could be used to tell you names of constraints and also
help you figure out what dependencies exist for the thing you are
trying to drop. (There are a bunch of views named whatever_USAGE that
tell you about dependencies for views, domains, constraints, etc.)

> Or more to the point, how does one
> write a transportable, standard compliant program that
> creates some tables, gobbles the dependencies, and then
> deletes them? I guess if all constraint names are made
> explicit, then there is no problem. But why have a syntactic
> variation that leads to the path of perdition? I mean,
> creating syntax to drive the user nuts or to trick him into
> doing something absolutely dreadful to himself is good sport,
> but...

If, for the moment, you don't think about actually using this stuff
... [OK, are you not thinking now? Are you sure? ...] then the rules
for conformance to Entry level SQL92 actually don't let you get into
this bind, and neither do the rules for Intermediate level.

At Entry level, sure the vendor doesn't have to support explicit names
for constraints, but he also does not have to support DROP statements.
[Ta da!!]

At Intermediate level, the vendor must support DROP statements, but
also must support explicit constraint names -- of course, the names
are optional, so the user is still given opportunity to put foot in
front of shot, but that could be seen as a basic human right, I
figure.

The problem occurs when vendors support the DROP statement, but do not
support explicit naming of constraints. But we wouldn't know any
vendors like that, would we?

[Having left holes big enough to drive a Hummer through, I think
the prudent thing is for me to go hide in the basement. ]

> >Also, according to the SQL standard, the syntax for DROP statements
> >(DROP CONSTRAINT, DROP TABLE, DROP COLUMN, etc.) includes a <drop
> >behavior> clause. Drop behavio[u]r can be RESTRICT or CASCADE.
> >
>
> Ms. Brown, it is, as always, a delight to hear from yo[u].

:-)

> Just remember, had you applied yourselves a little harder
> in 1812, you could have nipped our spelling pecularities
> in the bud.

Yah, I suppose, and if I'd applied myself a little harder in
first-year Chemistry class, I could have avoided this SQL stuff
altogether :-) (But then think of all the delightful conversations I
would have missed o[u]t on?)

> Have a good time with Ambassador Celluci. Your loss is our
> gain. Just don't take his advice on highway construction.

Thank you for the heads up, and for your concern with our well-being
:-)

Regards,
db