Re: Multiple specification of constraints

The world rejoiced as "Eric Kaun" <ekaun_at_yahoo.com> wrote:
> Even without the DB runtime, it's not rocket science to generate UIs (e.g.
> the HTML drop-downs and such) for a given hierarchy superimposed over your
> relational database (e.g. a view of the data starting with relation X and
> extending to relations Y and Z). But it would definitely be much, much
> better to do so.

It may be useful to consider a few examples as archetypes.

For instance, it is common for an address relation to have a "country" field, which might be validated against a table of ISO 3166 country codes.

In the database it is entirely appropriate to have a foreign key relationship pointing from the "address" relation to the "country" relation:

create table country (
  id char(2) not null unique,
  long_name text
);

create table address (
  id integer not null unique,
  street text,
  city text,
  region text,
  country char(2) not null references country(id) );

If you try to insert an entry with an invalid country, the DBMS will complain; no biggie.

It would be nice for a web interface to have a country selector based on the contents of the country relation so that you don't have to wait to try to insert data into the database before discovering that it was garbage. Indeed, that approach can be sufficiently successful to tempt people to drop the foreign key constraint.

What would be valuable would be to have a way to take constraints like this and assemble some sort of "bundle" of data about the constraint.

Client software might do what it wishes with this data to do some validation before the data gets to the database.

The "bad part" is that the list of country codes is ephermal data; there needs to be some way to "reset things" if a new country gets added. (There has been some "action" in ISO 3660 in the last year vis-a-vis the area of the world formerly known as Yugoslavia...)

But having the capability to get at the constraint information in a coherent fashion certainly would be handy because on a web form or a GUI dialog, you'll want to have some "constrained" choices as part of the UI.

-- 
output = reverse("moc.enworbbc" "_at_" "enworbbc")
http://www.ntlug.org/~cbbrowne/nonrdbms.html
"...I'm not one  of those who think Bill Gates is  the devil. I simply
suspect that if Microsoft ever met up with the devil, it wouldn't need
an interpreter." -- Nicholas Petreley, InfoWorld, Sept 16, 1996