ALLBASE/SQL Reference Manual (36216-90216)

364 Chapter10
SQL Statements A - D
CREATE TABLE
3. Creating a table with a hash structure
BEGIN WORK
Statements to create a DBEFile and add it to a DBEFileSet should be in the same
transaction as the statement to create the hash structure. This makes it impossible for
other transactions to use the new DBEFile for hashing before the hash structure is
created.
CREATE DBEFILE PurchHashF1 WITH PAGES = 120,
NAME = 'PurchHF1',
TYPE = TABLE
ADD DBEFILE PurchHashF1
TO DBEFILESET PurchFS
CREATE PUBLIC TABLE PurchDB.Vendors
(VendorNumber INTEGER NOT NULL,
VendorName CHAR(30) NOT NULL,
ContactName CHAR(30),
PhoneNumber CHAR(15),
VendorStreet CHAR(30) NOT NULL,
VendorCity CHAR(20) NOT NULL,
VendorState CHAR(2) NOT NULL,
VendorZipCode CHAR(10) NOT NULL,
VendorRemarks VARCHAR(60) )
UNIQUE HASH ON (VendorNumber) PAGES = 101
IN PurchFS
COMMIT WORK
4. Specify a DBEFileSet for a Check Constraint in the Column Definition
CREATE PUBLIC TABLE RecDB.Events
(SponsorClub CHAR(15),
Event CHAR(30),
Date DATE DEFAULT CURRENT_DATE,
CHECK (Date >= '1990-01-01') CONSTRAINT Events_Date_Ck
IN RecFS,
Time TIME,
Coordinator CHAR(20),
Results LONG VARBINARY(10000) IN LongFS,
FOREIGN KEY (Coordinator, SponsorClub)
REFERENCES RecDB.Members (MemberName, Club)
CONSTRAINT Events_FK)
IN RecFS;