ALLBASE/SQL Reference Manual (36216-90216)

Chapter 10 363
SQL Statements A - D
CREATE TABLE
Now only the DBA and members of authorization group Accounting can access the
table. Later, the accounting department manager is given control.
TRANSFER OWNERSHIP OF PurchDB.SupplyPrice TO MgrAcct
2. Creating a table using constraints and LONG columns
In this example, the tables are created with the PUBLIC option so as to be accessible to
any user or program that can start a DBE session. RecDB.Clubs defines those clubs
which can have members and hold events, as shown by the constraint Members_FK.
RecDB.Members defines those members who can have events for certain clubs, as
shown by constraint Events_FK. The LONG column Results is used to hold a text file
containing the results of a completed event. No date can be entered for an event that is
prior to the current date. RecDB.Members and RecDB.Events are both created
PUBLICROW since they are small tables on which a large amount of concurrent access
is expected.
CREATE PUBLIC TABLE RecDB.Clubs
(ClubName CHAR(15) NOT NULL
PRIMARY KEY CONSTRAINT Clubs_PK,
ClubPhone SMALLINT,
Activity CHAR(18))
NOT CASE SENSITIVE
IN RecFS
CREATE PUBLICROW TABLE RecDB.Members
(MemberName CHAR(20) NOT NULL,
Club CHAR(15) NOT NULL,
MemberPhone SMALLINT,
PRIMARY KEY (MemberName, Club) CONSTRAINT Members_PK,
FOREIGN KEY (Club) REFERENCES RecDB.Clubs (ClubName)
CONSTRAINT Members_FK)
IN RecFS
CREATE PUBLICROW TABLE RecDB.Events
(SponsorClub CHAR(15),
Event CHAR(30),
Date DATE DEFAULT CURRENT_DATE,
CHECK (Date >= '1990-01-01') CONSTRAINT Events_Date_Ck,
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