Evan Carlisle wrote:
Would anyone have a working example about creating a "base" table
with 3 text fields with one sql command (from tools:sql). Apparently I have
a syntax bug.
Also, a basic example of inserting data into this table would be useful.
thx.,
Evan
Assuming you are talking about a native Base database ( an HSQLdb
database in other words ) then the SQL is very much the SQL standard and
just about any reference on the internet will give you the proper
syntax. Particularly the user manual at http://hsqldb.org
That said - here is a small snippet from the Schema create file for an
upcoming Base tutorial:
-- TransactionType is a lookup table
-- used by the patient billing
-- funcitons
-- It is referecned as a FK
-- from the Account table
--
CREATE TABLE "TransactionType" (
"Tran_Type_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL
PRIMARY KEY, -- Transaction Type ID (Primary Key)
"Tran_Desc" VARCHAR(50),
-- Transaction Description
"Debit" CHAR(6) NOT NULL,
-- Debit
CONSTRAINT CK_DBT CHECK("Debit" IN ('DEBIT', 'CREDIT'))
);
--
-- Intial values for TransactionType
-- table
--
INSERT INTO "TransactionType" ("Tran_Desc", "Debit" ) VALUES( 'Session',
'DEBIT' );
INSERT INTO "TransactionType" ("Tran_Desc", "Debit" ) VALUES( 'Missed Session',
'DEBIT' );
INSERT INTO "TransactionType" ("Tran_Desc", "Debit" ) VALUES( 'Payment',
'CREDIT' );
INSERT INTO "TransactionType" ("Tran_Desc", "Debit" ) VALUES( 'Returned
Payment', 'DEBIT' );
FYI the charcters '--' denote comments and anything on the line after
this is treated as a comment.
I would point out that the final line in the TABLE create statement,
CONSTRAINT CK_DBT CHECK("Debit" IN ('DEBIT', 'CREDIT')), tells the
database engine to check any value being written to the column "Debit"
and only values of the string literals 'DEBIT' or 'CREDIT' any other
value will be rejected. Otherwise this is a pretty basic create
statement. By creating the constraint in the SQL statement it will be
enforced no matter how the table is updated - in a table data view (
double clicking on the table name in Base), in a form attached to the
table, or via a SQL command in the SQL window.
HTH
Drew
--
OpenOffice.org User Community Forum: http://user.services.openoffice.org
United States PostgreSQL Association: http://www.postgresql.us/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
