CREATE TABLE – SQL Command Example

The following example creates a new database named Mydata1. CREATE TABLE is used to create three tables (Salesman, Customer, and Orders). The FOREIGN KEY and REFERENCES clauses in the second CREATE TABLE command create a persistent one-to-many relationship between the Salesman and Customer tables. The DEFAULT clauses in the third CREATE TABLE command establish default values, and  the CHECK and ERROR clauses establish business rules for entering data into specific fields. The MODIFY DATABASE is used to display the relationship between the three tables.

CLOSE DATABASES
CLEAR

* Create mydata database in the current directory or folder
CREATE DATABASE mydata1

* Create a salesman table with a primary key
CREATE TABLE salesman ;
   (SalesID c(6) PRIMARY KEY, ;
   SaleName C(20))

* Create a customer table and relate it to the salesman table.
CREATE TABLE customer ;
   (SalesID c(6), ;
   CustId i PRIMARY KEY, ;
   CustName c(20) UNIQUE,   ;
   SalesBranch c(3), ;
   FOREIGN KEY SalesId TAG SalesId REFERENCES salesman)

* Create an orders table related to customer with its own primary
* key and some business rules such as defaults & checks.
CREATE TABLE orders ;
   (OrderId i PRIMARY KEY, ;
      CustId i REFERENCES customer TAG CustId, ;
      OrderAmt y(4), ;
      OrderQty i ;
      DEFAULT 10 ;
      CHECK (OrderQty > 9) ;
      ERROR "Order Quantity must be at least 10", ;
         DiscPercent n(6,2) NULL ;
      DEFAULT .NULL., ;
      CHECK (OrderAmt > 0) ERROR "Order Amount Must be > 0" )

* Display new database, tables, and relationships
MODIFY DATABASE

* Delete example files
SET SAFETY OFF && To suppress verification message
CLOSE DATABASES     && Close database before deleting
DELETE DATABASE mydata1 DELETETABLES