Hi Drew,
Thanks for that point of clarification re what is in HSQLdb, and how much of
that is supported by the Base GUI.
In a message dated 2009.04.01 17:12 -0500, Drew Jensen wrote:
Before I give up, has anyone found a way to mimic this limited used of
enum fields, and to dispose of that Base error without giving up the
requirement for a Priority estimate?
OK - first I take it that what you are talking about here is Base with
an embedded database as opposed to working against MySQL or Postgres.
Yes, with the embedded HSQLdb. Honestly (I'm new to Base), I never thought
about using it any other way.
In this case HSQLdb does not support an ENUM field type.
Oh thanks, I didn't realize that - but should have realized the limitation
might be more than the front end. However, it was nice of you here to point
out how a front-end limitation fails to expose the CHECK constraints, and
how to get around that:
However it does indeed support constraints on table columns, including a
CHECK constraint.
...
Assume I have a Table that I want to add that priority column to.
In the SQL window I would use TWO commands, separated by semi-colon
ALTER TABLE "Table1" ADD COLUMN "Priority" NUMERIC(1,0) DEFAULT 1 NOT NULL;
ALTER TABLE "Table1" ADD CHECK( "Priority" > 0 AND "Priority" < 6 );
That's it. Notice the slight syntax changes in my statements.
Terrific! BTW: upon seeing your example, I first tried
ALTER TABLE "Table1" ADD CHECK( 0 < "Priority" < 6 );
just to get a feel if the syntax supports that. As you already knew, it
does not.
Also, it seems that some SQL implementations (like HSQL) require quotes
around table and column names, and some do not; similarly, some will let you
slide on identifiers like COLUMN before the column name. It just occurred
to me (having been not always tidy about this), that /any/ parser will
accept quotes and other formalities, so they should become part of basic
practice. So your example served a second purpose, reminding that keeping
the grammar clean is worth a few keystrokes. Thanks.
Best regards,
John
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
