Example 1: Creating new tables with primary and foreign keys
In this example, the CREATE TABLE statement is used to create
the EMPLOYEES_2, SALARY_HISTORY_2, and WORK_STATUS_2 tables in
the personnel database. It specifies column definitions based on
domain definitions for the entire database.
The FOREIGN KEY constraint specified in the SALARY_HISTORY_2
table must match the PRIMARY KEY constraint specified in the
EMPLOYEES_2 table.
Note also that the CHECK constraint specified is a table
constraint because it is separated by commas from the column to
which it refers. In this case, a column constraint on EMPLOYEE_ID
would have the same effect because it refers only to the single
column EMPLOYEE_ID.
Because the dialect is SQL99, the default for constraint
evaluation time is NOT DEFERRABLE.
SQL> -- *** Set Dialect ***
SQL> --
SQL> SET DIALECT 'SQL99';
SQL> --
SQL> -- *** Create tables ***
SQL> --
SQL> CREATE TABLE WORK_STATUS_2
cont> (
cont> STATUS_CODE STATUS_CODE_DOM
cont> CONSTRAINT WS2_STATUS_CODE_PRIMARY
cont> PRIMARY KEY,
cont> STATUS_NAME STATUS_NAME_DOM,
cont> STATUS_TYPE STATUS_DESC_DOM
cont> );
SQL> --
SQL> CREATE TABLE EMPLOYEES_2
cont> (
cont> EMPLOYEE_ID ID_DOM
cont> CONSTRAINT E2_EMPLOYEE_ID_PRIMARY
cont> PRIMARY KEY,
cont> LAST_NAME LAST_NAME_DOM,
cont> FIRST_NAME FIRST_NAME_DOM,
cont> MIDDLE_INITIAL MIDDLE_INITIAL_DOM,
cont> ADDRESS_DATA_1 ADDRESS_DATA_1_DOM,
cont> ADDRESS_DATA_2 ADDRESS_DATA_2_DOM,
cont> CITY CITY_DOM,
cont> STATE STATE_DOM,
cont> POSTAL_CODE POSTAL_CODE_DOM,
cont> SEX SEX_DOM
cont> CONSTRAINT EMPLOYEE_SEX_VALUES
cont> CHECK (
cont> SEX IN ('M', 'F') OR SEX IS NULL
cont> ),
cont> BIRTHDAY DATE_DOM,
cont> STATUS_CODE STATUS_CODE_DOM
cont> CONSTRAINT E2_STATUS_CODE_FOREIGN
cont> REFERENCES WORK_STATUS_2 (STATUS_CODE),
cont> CONSTRAINT EMP_STATUS_CODE_VALUES_2
cont> CHECK (
cont> STATUS_CODE IN ('0', '1', '2')
cont> OR STATUS_CODE IS NULL
cont> )
cont> );
SQL> --
SQL> CREATE TABLE SALARY_HISTORY_2
cont> (
cont> EMPLOYEE_ID ID_DOM
cont> CONSTRAINT SH2_EMPLOYEES_ID_FOREIGN
cont> REFERENCES EMPLOYEES_2 (EMPLOYEE_ID),
cont> SALARY_AMOUNT SALARY_DOM,
cont> SALARY_START DATE_DOM,
cont> SALARY_END DATE_DOM
cont> );
SQL>
Example 2: Creating a table with many SQL data types
The following example is an excerpt from the sample program sql_
all_datatypes created during installation of Oracle Rdb in the
Samples directory. For a variety of languages, sql_all_datatypes
illustrates how you declare program variables to match a variety
of data types, and how you can specify those variables in SQL
statements when you store and retrieve column values or null
values.
This example shows the CREATE TABLE statement from the sql_all_
datatypes program.
EXEC SQL CREATE TABLE ALL_DATATYPES_TABLE
(
CHAR_COL CHAR(10),
SMALLINT_COL SMALLINT,
SMALLINT_SCALED_COL SMALLINT (3),
INTEGER_COL INTEGER,
INTEGER_SCALED_COL INTEGER (2),
QUADWORD_COL QUADWORD,
QUADWORD_SCALED_COL QUADWORD (5),
REAL_COL REAL,
DOUBLE_PREC_COL DOUBLE PRECISION,
DATE_COL DATE,
VARCHAR_COL VARCHAR(40)
);
Example 3: Specifying default values for columns
The following example illustrates the use of default values
for columns. Each salesperson enters his or her own daily sales
information into the DAILY_SALES table.
SQL> --
SQL> CREATE TABLE DAILY_SALES
cont> --
cont> -- The column SALESPERSON is based on LAST_NAME_DOM and
cont> -- the default value is the user name of the person who
cont> -- enters the information:
cont> (SALESPERSON LAST_NAME_DOM DEFAULT USER,
cont> --
cont> -- Typical work day is 8 hours:
cont> HOURS_WORKED SMALLINT DEFAULT 8,
cont> HOURS_OVERTIME SMALLINT,
cont> GROSS_SALES INTEGER );
SQL> --
SQL> -- Insert daily sales information accepting the
SQL> -- default values for SALESPERSON and HOURS_WORKED:
SQL> --
SQL> INSERT INTO DAILY_SALES
cont> (HOURS_OVERTIME, GROSS_SALES )
cont> VALUES
cont> (1, 2499.00);
1 row inserted
SQL> SELECT * FROM DAILY_SALES;
SALESPERSON HOURS_WORKED HOURS_OVERTIME GROSS_SALES
KILPATRICK 8 1 2499
1 row selected
Example 4: Violating a constraint indirectly with the DELETE
statement
Constraints prevent INSERT statements from adding rows to a table
that do not satisfy conditions specified in the constraint.
Constraints also prevent DELETE or UPDATE statements from
deleting or changing values in a table if the deletion or change
violates the constraint on another table in the database. The
following example illustrates that point:
SQL> -- TEST has no constraints defined for it, but it is subject to
SQL> -- restrictions nonetheless because of the constraint specified
SQL> -- in TEST2:
SQL> CREATE TABLE TEST
cont> (COL1 REAL);
SQL>
SQL> CREATE TABLE TEST2
cont> (COL1 REAL,
cont> CHECK (COL1 IN
cont> (SELECT COL1 FROM TEST))
cont> );
SQL> COMMIT;
SQL>
SQL> INSERT INTO TEST VALUES (1);
1 row inserted
SQL> INSERT INTO TEST2 VALUES (1);
1 row inserted
SQL> COMMIT;
SQL> -- This DELETE statement will fail because it will cause COL1 in
SQL> -- TEST2 to contain a value without the same value in COL1 of TEST:
SQL> DELETE FROM TEST WHERE COL1 = 1;
1 row deleted
SQL> COMMIT;
%RDB-E-INTEG_FAIL, violation of constraint TEST2_CHECK1 caused operation to
fail
Example 5: Evaluating constraints at verb time
Deferrable constraints are not evaluated until a transaction
issues a COMMIT statement. You can specify that constraints be
evaluated more frequently with the EVALUATING clause of the SET
TRANSACTION statement.
SQL> create table TEST
cont> (col1 integer,
cont> col2 integer
cont> constraint C2
cont> unique
cont> deferrable
cont> );
SQL>
SQL> insert into TEST (col1, col2) values (1, 2);
1 row inserted
SQL> commit;
SQL>
SQL> /*
***> This INSERT will violate the constraint as shown by
***> the error during COMMIT
***> */
SQL> insert into TEST (col1, col2) values (1, 2);
1 row inserted
SQL> commit;
%RDB-E-INTEG_FAIL, violation of constraint C2 caused operation to fail
-RDB-F-ON_DB, on database USER_DISK:[DOC.DATABASES]MF_PERSONNEL.RDB;1
SQL> /*
***> The COMMIT failed, so we will ROLLBACK
***> */
SQL> rollback;
SQL>
SQL> /*
***> You can change the evalution time using the EVALUATING
***> clause of SET TRANSACTION
***> */
SQL> set transaction read write evaluating C2 at verb time;
SQL> insert into TEST (col1, col2) values (1, 2);
%RDB-E-INTEG_FAIL, violation of constraint C2 caused operation to fail
-RDB-F-ON_DB, on database USER_DISK:[DOC.DATABASES]MF_PERSONNEL.RDB;1
SQL> rollback;