SQL-92 Data Definition Statements
5.1 CREATE TABLE and DROP TABLE Statements
5.1.2 DROP TABLE
The DROP TABLE statement deletes all data and indexes for a table and erases its entry in the system catalog.
EXAMPLE
The following code fragment shows a DROP TABLE statement to drop the tmp_customer table:
/*
** CONNECT TO the DEFAULT database */
EXEC SQL
CONNECT TO DEFAULT ; /*
** Drop the tmp_customer table from the database. */
EXEC SQL
DROP TABLE tmp_customer ; if (sqlca.sqlcode < 0)
{
printf ("DROP TABLE statement failed (%ld : %s) \n", sqlca.sqlcode, sqlca.sqlerrm) ;
EXEC SQL ROLLBACK WORK ; EXEC SQL DISCONNECT DEFAULT ; exit (1) ;
} /*
** Successful DROP; COMMIT the DROP operation. */
EXEC SQL
COMMIT WORK ;
printf ("Dropped tmp_customer table. \n"); /*
** DISCONNECT from the DEFAULT database */
EXEC SQL
SQL-92 Data Definition Statements
5–5
5.2
CREATE INDEX and DROP INDEX Statements
This section illustrates how to CREATE an index and DROP an index using ESQL.
5.2.1
CREATE INDEX
The CREATE INDEX statement creates an index on one or more columns of a table. The purpose of an index is to improve the retrieval time for rows in a table. You can specify an index in ascending order (ASC) or descending order (DESC).
EXAMPLE
The index in this CREATE INDEX example is specified on the single column cust_no, and is of ascending order on the value of the column:
/*
** CONNECT to the DEFAULT database. */
EXEC SQL
CONNECT TO DEFAULT ; /*
** Create the idx_cust.index. */
EXEC SQL
CREATE INDEX idx_cust ON customer (cust_no ASC) ; if (sqlca.sqlcode < 0)
{
printf ("CREATE INDEX idx_cust statement failed (%ld : %s) \n", sqlca.sqlcode, sqlca.sqlerrm);
EXEC SQL ROLLBACK WORK ; EXEC SQL DISCONNECT DEFAULT ; exit (1);
} /*
** COMMIT the CREATE INDEX operation. */
EXEC SQL COMMIT WORK ;
printf ("Index idx_cust created. \n"); /*
** DISCONNECT from the DEFAULT database */
EXEC SQL
5.2.2
DROP INDEX
Use the DROP INDEX statement to drop an index on a table.
EXAMPLE
The following code fragment shows a DROP INDEX statement, dropping the idx_cust index on the customer table:
/*
** CONNECT to the DEFAULT database. */
EXEC SQL
CONNECT TO DEFAULT ; /*
** DROP the idx_cust index. */
EXEC SQL
DROP INDEX idx_cust ON customer; if (sqlca.sqlcode < 0)
{
printf ("DROP INDEX statement failed (%ld : %s) \n", sqlca.sqlcode, sqlca.sqlerrm);
EXEC SQL ROLLBACK WORK ; EXEC SQL DISCONNECT DEFAULT ; exit (1);
} /*
** Successful drop; commit the drop index operation. */
EXEC SQL COMMIT WORK ;
printf ("DROP INDEX idx_cust successful.\n"); /*
** Disconnect from the default database. */
EXEC SQL
SQL-92 Data Definition Statements
5–7
5.3
CREATE VIEW and DROP VIEW Statements
This section discusses how to CREATE a view and DROP a view in an ESQL program.
5.3.1
CREATE VIEW
Use the CREATE VIEW statement to create a view on existing tables or views. You specify the name for the VIEW.
EXAMPLE
The following code fragment shows how to use a CREATE VIEW statement in an ESQL program. This CREATE VIEW statement creates the ne_customer view of the customer table:
/*
** CONNECT to the DEFAULT database. */
EXEC SQL
CONNECT TO DEFAULT ; /*
** Create the ne_customer view. */
EXEC SQL
CREATE VIEW ne_customer AS
SELECT cust_no, last_name, street, city, state FROM customer
WHERE state in ('NH', 'MA', 'ME', 'CT', 'RI', 'VT') ; if (sqlca.sqlcode < 0)
{
printf ("CREATE VIEW statement failed (%ld : %s) \n", sqlca.sqlcode, sqlca.sqlerrm) ;
EXEC SQL ROLLBACK WORK ; EXEC SQL DISCONNECT DEFAULT ; exit (1) ;
} /*
** Successful create view; commit the operation. */
EXEC SQL
COMMIT WORK ;
printf ("CREATE VIEW ne_customer successful. \n"); /*
** Disconnect from the default database. */
EXEC SQL
5.3.2
DROP VIEW
The DROP VIEW statement deletes an existing view from the database.
EXAMPLE
The following example drops the ne_customer view:
/*
** CONNECT to the DEFAULT database. */
EXEC SQL
CONNECT TO DEFAULT ; /*
** Drop the ne_customer view. */
EXEC SQL
DROP VIEW ne_customer ; if (sqlca.sqlcode < 0) {\n
printf ("DROP VIEW statement failed (%ld : %s) ", sqlca.sqlcode, sqlca.sqlerrm);
EXEC SQL ROLLBACK WORK ; EXEC SQL DISCONNECT DEFAULT ; exit (1);
} /*
** Successful drop; commit the operation. */
EXEC SQL
COMMIT WORK ;
printf ("DROP VIEW ne_customer successful. \n") ; /*
** Disconnect from the default database */
EXEC SQL
SQL-92 Data Definition Statements
5–9
5.4
Integrity Constraints
Integrity constraints are application rules that the database enforces. You define integrity constraints on base tables to ensure data integrity in a database. An integrity constraint can specify unique values for a column, validate values of a column, or provide referential integrity.
Referential integrity ensures that the relationships among different rows of the same or different tables are valid. For example, you might use referential integrity to ensure that a master row is inserted before a detail row, and a detail row is deleted before a master row.
5.4.1
Need for Integrity Constraints
Integrity constraints are necessary because data in a database must be valid and consistent at all times. Data might be inconsistent because of entry errors, duplicate entries of rows, or other violations.
To illustrate, consider a table containing employee information where the employee numbers must be unique. To ensure this, specify a UNIQUE constraint on the column that contains the employee number (emp_no):
See the “Candidate Keys” section for information on the UNIQUE keyword.
Similarly, for each order entry made in an orders table, you might want to ensure that the associated item entry is present in another table by specifying a referential integrity constraint on a column in the orders table. See the examples in the “Referential Constraints” section later in this chapter.
EXEC SQL
CREATE TABLE employee_info (
emp_no INTEGER NOT NULL UNIQUE, first_name VARCHAR(20) NOT NULL, last_name VARCHAR(20) NOT NULL, title VARCHAR(20)