SQL Bible [Electronic resources] نسخه متنی

Tables

Tables are the central and the most important
objects in any relational database. The primary purpose of any database is to
hold data that is logically stored in tables.

One of the relational database design
principles is that each table holds information about one specific type of
thing, or entity. For example, a
CUSTOMER table would contain data about
customers only, not about the products they ordered, invoices issued to them,
or salesmen who placed orders for them. The ACME database doesn't even have
customers' addresses and phone numbers because those are separate entities
represented by
ADDRESS and
PHONE tables respectively.

Rows (sometimes also called records) are
horizontal slices of data; each row contains data about one entity item. A row
from the
CUSTOMER table contains information about
one single customer, a row from the
ORDER_HEADER, about one single order, and
so on.

The vertical cuts of table data are called
columns. A column holds a particular type of information for all entity
records. The
CUST_NAME_S column in the
CUSTOMER table encloses all customers'
names;
ORDHDR_INVOICENBR_N in
ORDER_HEADER contains all invoice
numbers.

CREATE TABLE statement

Even though we can make some
generalizations about the database table creation process, internal table
implementations and
CREATE
TABLE statement clauses differ from
vendor to vendor significantly. For example, Oracle's
CREATE
TABLE syntax diagram is about fifteen
pages long; DB2's takes roughly seven pages; MS SQL Server has the shortest
definition — only one-and-a-half pages.

We are going to concentrate on the most
common clauses as described in the SQL99 standards with emphasis on vendor
implementation differences.

SQL99 syntax

SQL99 offers the following
CREATE
TABLE syntax:

CREATE [{GLOBAL | LOCAL}
TEMPORARY] TABLE <table_name> (
<column_name> [<domain_name> | <datatype>
[<size1>[,<size2>] ] [<column_constraint>,...] [DEFAULT
<default_value>] [COLLATE <collation_name>],...
[<table_constraints>] [ON COMMIT {DELETE | PRESERVE} ROWS]
)

Oracle 9i
syntax

In Oracle 9i, you
can create three different types of tables: relational tables, object tables,
and XML type tables. The latter two are out of scope of this book; the
simplified
CREATE
TABLE syntax for relational tables is
shown below:

CREATE [GLOBAL TEMPORARY]
TABLE [<schema>.]<table_name> ( <column_name>
<datatype> [<size1>[,<size2>]] [DEFAULT
<default_value>] [<column_constraint>,...],...
[<table_constraint>,...] [<physical_properties>] ) [ON COMMIT
{DELETE|PRESERVE} ROWS];

DB2 UDB 8.1 syntax

In DB2 UDB 8.1, you can create tables
using this syntax (some complex clauses are omitted):

CREATE TABLE
[<schema>.]<table_name> ( <column_name> <datatype>
[<size1>[,<size2>]] [<column_constraint>,...] [[WITH] DEFAULT
[<default_value>] | GENERATED {ALWAYS | BY DEFAULT } AS IDENTITY
[<identity_clause>] ],... [<table_constraint>,...] | [[LIKE
<table_name> [{INCLUDING | EXCLUDING} {[COLUMN] DEFAULTS | IDENTITY} ] ]
| [AS <select_statement> [{DEFINITION ONLY | DATA INITIALLY DEFERRED
REFRESH DEFERRED | IMMEDIATE } ] ] ] [<tablespace_options>]
)

MS SQL 2000 syntax

Here is MS SQL Server 2000
syntax:

CREATE TABLE
[[<database_name>.]<owner>.][#|##]<table_name> (
<column_name> <datatype> [<size1>[,<size2>]] [COLLATE
<collation_name>] [[DEFAULT <default_value>] | [IDENTITY [
([<seed>, <increment>])

[NOT FOR REPLICATION] ] ] [<column_constraint>,...],...
[<table_constraint>,...] [ON <filegroup>] [TEXTIMAGE_ON
<filegroup>] )

Permanent and temporary
tables

Database tables can be permanent or
temporary, based upon the lifespan of table data.

Usually you want tables to be permanent,
meaning that inserted data stays there until somebody explicitly deletes table
rows. In some less common situations, you may want the table data to disappear
as soon as one commits changes in other tables or logs
off. Typically, that may be the case when you are issuing SQL statements from
other programs (embedded SQL) or using procedural SQL extensions, such as
PL/SQL (Oracle) or Transact SQL (MS SQL Server) to perform complex tasks. For
example, you might want a program to select columns from dozens of different
tables, apply formulas to perform calculations on them, store the intermediate
results in a temporary table, and then update another group of tables based on
those results.

Temporary tables in SQL99

SQL99 mandates two types of temporary
tables:
LOCAL and
GLOBAL. The difference is in their
data visibility. Even though the data in a temporary table is visible only
within the session (or transaction)
that populated it,
GLOBAL tables can be accessed by any
program or module within the session; thus, a stored procedure
sp_1 can create the global temporary
table
TEMP_TABLE1; another stored procedure
sp_2 can populate it with data, and
then other stored procedures
sp_3,
sp_4, and
sp_5 can use the data in their
processing as long as all five stored procedures are started from the same
session.

Unlike temporary table data, the
temporary table's definition is permanent; so, if user A creates a temporary
table B, populates it with data, and logs off, when s/he logs back the next day
(or next year), the table is still there, but it is empty.

Temporary tables in Oracle
9i

You can create
GLOBAL
TEMPORARY tables in Oracle;
LOCAL
TEMPORARY tables are not yet
implemented. The idea of a
GLOBAL
TEMPORARY table is slightly different
from that described in the SQL99 concept. The table definition is visible to
all sessions, but the data in a temporary table is only visible to the session
that populated the data:

CREATE GLOBAL TEMPORARY
TABLE tmp_customer_order_totals ( customer_name VARCHAR2(30), customer_total
NUMBER ) ON COMMIT DELETE ROWS;

In other words, user John might have
created the
TMP_CUSTOMER_ORDER_TOTALS table a
year ago; users Mary, Susan, and Kyle are using the table concurrently
(assuming they have appropriate privileges), but from their point of view it's
like each of them was using his or her own temporary table; see
Figures
4-1,
4-2, and
4-3.

Figure 4-1: Mary's rows in the
TMP_CUSTOMER_ORDER_TOTALS temporary
table

Figure 4-2: Susan's rows in
the
TMP_CUSTOMER_ORDER_TOTALS temporary
table

Figure 4-3: Kyle's rows in the
TMP_CUSTOMER_ORDER_TOTALS temporary
table

Temporary tables in DB2 UDB
8.1

Temporary tables cannot be created in
DB2 UDB 8.1 with
CREATE
TABLE; you can instead declare a
temporary table for current session with the
DECLARE
GLOBAL
TEMPORARY
TABLE statement.

The declared temporary table cannot
be shared with other sessions; when a session is terminated, rows and the table
definition are both dropped.

The syntax of the
DECLARE
GLOBAL
TEMPORARY
TABLE statement is similar to DB2's
CREATE
TABLE statement; here is an example:

DECLARE GLOBAL TEMPORARY
TABLE tmp_customer_order_totals ( customer_name VARCHAR(30), customer_total
DECIMAL(12,2) ) ON COMMIT PRESERVE ROWS NOT LOGGED

Temporary tables in MS SQL
Server 2000

The MS SQL Server syntax used to
create a temporary table is not consistent with SQL99 standards. To create a
local temporary table, you prefix it with the pound sign (#); the double pound sign (##) indicates a global temporary table.

Local temporary tables are visible
only to the current session; both the table data and table definition are
deleted when the user logs off (comparable to DB2 temporary tables created with
the
DECLARE
GLOBAL
TEMPORARY
TABLE statement):

CREATE TABLE
#tmp_customer_order_totals ( customer_name VARCHAR(30), customer_total MONEY
)

Global temporary tables are visible
to all users; they are destroyed after every user who was referencing the table
disconnects from the SQL Server:

CREATE TABLE
##tmp_customer_order_totals ( customer_name VARCHAR(30),
customer_total MONEY )

Column definitions

The table has to have one or more
column definitions, which consist of the column name and the data type.

SQL99

According to SQL99, a
domain can be used for a column instead of a data type.
(Domains are covered later in this chapter.)

customer_name
VARCHAR2(30) customer_total NUMBER

customer_name VARCHAR(30)
customer_total MONEY

Column constraints

Each column can have one or more column
constraints. SQL99 specifies the following column constraints:

NOT
NULL means that the
NULL values are not permitted in
the column.

UNIQUE means all values in the
column must be distinct values;
NULLs are permitted.

PRIMARY
KEY specifies that all column
values must be unique and the column can't contain
NULLs. In other words, it's a
combination of the above two constraints.

REFERENCES means the column is
a foreign key to the referenced table.

CHECK verifies that the column
values obey certain rules; for example, only positive numbers are permitted, or
only a certain set of strings is valid.

All three implementations have all the
above constraints and handle them in similar ways. You can either name
constraints accordingly with your database naming conventions, or don't specify
constraint names at all. In the latter case, RDBMS will generate default names.
The following examples illustrate the use of column constraints (Oracle or MS
SQL Server):

CREATE
TABLE salesman ( salesman_id_n INT CONSTRAINT pk_salesmanprim PRIMARY
KEY, salesman_code_s VARCHAR (2) CONSTRAINT uk_salescode
UNIQUE, salesman_name_s VARCHAR (50) NOT NULL,
salesman_status_s CHAR (1) CONSTRAINT chk_salesstatus
CHECK  (salesman_status_s in ('N', 'Y')) )
CREATE TABLE address (  addr_id_n INT CONSTRAINT pk_addrprimary
PRIMARY KEY, addr_custid_fn INT,  addr_salesmanid_fn INT
CONSTRAINT fk_addr_salesman REFERENCES salesman (salesman_id_n),
addr_address_s VARCHAR(60),  addr_type_s VARCHAR(8) CONSTRAINT
chk_addr_type CHECK  (addr_type_s IN ('BILLING',
'SHIPPING')),  addr_city_s VARCHAR(18) CONSTRAINT
nn_addr_city NOT NULL, addr_state_s CHAR(2),  addr_zip_s
VARCHAR(10) NOT NULL, addr_country_s CHAR(3) )

You would need to make a couple of
modifications for this statement to run in DB2:

salesman_id_n INT CONSTRAINT pk_salesmanprim PRIMARY KEY NOT
NULL salesman_code_s VARCHAR (2) CONSTRAINT uk_salescode UNIQUE
NOT NULL addr_id_n INT CONSTRAINT pk_addrprimary PRIMARY
KEY NOT NULL

Column default values

Each column can optionally be given a
default value (in range of its data type). In this case, if an
INSERT statement omits the column, the
default value will automatically be populated:

CREATE TABLE product (
prod_id_n INTEGER NOT NULL, prod_price_n DECIMAL(10,2), prod_num_s VARCHAR(10),
prod_description_s VARCHAR(44) NOT NULL, 
prod_status_s CHAR(1) DEFAULT 'Y', prod_brand_s VARCHAR(20) NOT
NULL, prod_pltwid_n DECIMAL(5,2) NOT NULL, prod_pltlen_n DECIMAL(5,2) NOT NULL,
prod_netwght_n DECIMAL(10,3), prod_shipweight_n DECIMAL(10,3) ) INSERT INTO
product ( prod_id_n, prod_price_n, prod_num_s, prod_description_s,
prod_brand_s, prod_pltwid_n, prod_pltlen_n, prod_netwght_n, prod_shipweight_n )
VALUES (990, 18.24, '990', 'SPRUCE LUMBER 30X40X50', 'SPRUCE LUMBER', 4, 6,
21.22577, 24.22577 ) SELECT prod_id_n, prod_price_n, prod_status_s FROM product
PROD_ID_N PROD_PRICE_N PROD_STATUS_S --------- ------------ ------------- 990
18.24 Y

This example will work with all our
"big three" databases.

Column collating sequence

Character string columns can optionally
have a collating sequence; thus, you can specify nondefault character
precedence order.

Out of our three database vendors only
MS SQL Server allows collations as a part of the column definition. The
collation name specifies the collation for the column of a character string
data type; you can use both MS SQL Server and MS Windows predefined collations.
For example, if you want all customer names in the ACME database to be handled
according to French collation rules, you can modify the
CUSTOMER table:

CREATE
TABLE customer ( cust_id_n INT NOT NULL, cust_paytermsid_fn INT,
cust_salesmanid_fn INT, cust_status_s VARCHAR(1) DEFAULT 'Y' NOT NULL,
cust_name_s VARCHAR(50) COLLATE FRENCH_CI_AI NOT NULL,
cust_alias_s VARCHAR(15), cust_credhold_s VARCHAR(1) DEFAULT 'Y' NOT NULL
)

Table constraints

Table constraints are similar to column
constraints; the main difference is that table constraints can be used not only
on individual columns, but also on column lists. The valid table constraints
are listed here:

UNIQUE. Similar to the column
constraint, but can ensure uniqueness of the combination of two or more
columns.

PRIMARY
KEY. The combination of values in
constrained column(s) must be unique; NULL values are not allowed.

FOREIGN
KEY. Specifies a column or group of
columns in the table vhat references a column (or group of columns) in the
referenced table.

CHECK. Defines a predicate that
refers values in one or more tables; similar to the column
CHECK constraint.

The following example illustrates the
use of table constraints:

CREATE
TABLE order_header ( ordhdr_id_n INTEGER NOT NULL, ordhdr_payterms_fn INTEGER,
ordhdr_statusid_fn INTEGER, ordhdr_custid_fn INTEGER, ordhdr_salesmanid_fn
INTEGER, ordhdr_nbr_s VARCHAR(30) NOT NULL, ordhdr_invoicenbr_n
INTEGER, ordhdr_orderdate_d DATETIME, ordhdr_invoicedate_d DATETIME,
ordhdr_canceldate_d DATETIME, ordhdr_credithold_s CHAR(1),
ordhdr_readytoinvoice_s CHAR(1) DEFAULT 'N', ordhdr_notes_s VARCHAR(60),
ordhdr_createdby_s VARCHAR(10), ordhdr_createdate_d DATETIME, 
CONSTRAINT chk_ordhdr_ready CHECK 
(ordhdr_readytoinvoice_s IN ('N', 'Y')),  CONSTRAINT
chk_ordhdr_credh CHECK  (ordhdr_credithold_s IN ('N',
'Y')),  CONSTRAINT pk_ordhdrprim PRIMARY KEY
(ordhdr_id_n),  CONSTRAINT idx_ordhdr_ordnbr UNIQUE
(ordhdr_nbr_s) ) CREATE TABLE shipment ( shipment_id_n INTEGER NOT
NULL, shipment_bolnum_s VARCHAR(6), shipment_shipdate_d DATETIME,
shipment_arrivdate_d DATETIME, shipment_totalcases_n INTEGER,
shipment_trailernbr_s VARCHAR(12), shipment_shpmntfrght_n DECIMAL(12,2),
shipment_frtterms_s VARCHAR(3), shipment_createdby_s VARCHAR(10),
shipment_createdate_d DATETIME,  CONSTRAINT chk_shipfrtterms
CHECK  (shipment_frtterms_s IN ('COL', 'PPD')),
 CONSTRAINT pk_shipmentrprim PRIMARY KEY (shipment_id_n) )
CREATE TABLE order_shipment ( ordship_ordhdr_id_fn INTEGER NOT NULL,
ordship_shipment_id_fn INTEGER NOT NULL,  CONSTRAINT pk_ordhdrship
PRIMARY KEY  (ordship_ordhdr_id_fn,
ordship_shipment_id_fn),  CONSTRAINT fk_ordsh_ord FOREIGN
KEY (ordship_ordhdr_id_fn)  REFERENCES
order_header(ordhdr_id_n) )

This example is for MS SQL Server
syntax; to make it work in Oracle 9i or DB2 UDB 8.1,
modify the
DATETIME data type for columns
ORDHDR_ORDERDATE_D,
ORDHDR_INVOICEDATE_D,
ORDHDR_CANCELDATE_D,
ORDHDR_CREATEDATE_D,
SHIPMENT_SHIPDATE_D,
SHIPMENT_ARRIVDATE_D, and
SHIPMENT_CREATEDATE_D to be of a valid
data type for the particular implementation (DATE
or
TIMESTAMP).

Referential integrity
constraints optional clauses

SQL99 assumes
an optional clause on the creation of
FOREIGN
KEY and
REFERENCES constraints that specifies
what happens to a row if that row has a referential relationship and the
referenced row is deleted from the parent table or changes are made to the
referenced table. The syntax is as follows:

[ON DELETE {NO ACTION |
CASCADE | SET NULL}] [ON UPDATE {NO ACTION | CASCADE | SET NULL | SET
DEFAULT}]

NO
ACTION is the default behavior and
means an error will be generated if one tries to delete the row (or update the
primary key value) referenced by other table(s).

CASCADE
assumes that the same changes will automatically be done to the foreign key as
were made to the parent; thus, if the parent row is deleted, all child rows
that referenced it will also be deleted; if the parent's primary key is
updated, all child rows' foreign keys that reference it will also be updated
with the same value.

SET
NULL means if the parent row was
deleted or its primary key was changed, all child tables' referencing foreign
key values will be set to
NULL.

SET
DEFAULT is very similar to
SET
NULL, but the child tables' columns
are set to their default values rather than to
NULLs. This assumes that column
default values exist.

All our three vendors implemented the
above SQL99 standard to certain degree.

Oracle
9i does not have the
ON
UPDATE clause; the
ON
DELETE clause syntax is:

[ON DELETE { NO ACTION |
CASCADE | SET NULL }]

DB2 UDB
8.1 has an additional optional
RESTRICT that in most cases behaves
like
NO
ACTION:

[ON DELETE {NO ACTION |
RESTRICT | CASCADE | SET NULL}] [ON UPDATE {NO ACTION |
RESTRICT}]

MS SQL Server
2000 has two options —
NO
ACTION and
CASCADE for both
ON
DELETE and
ON
UPDATE clauses:

[ON DELETE {NO ACTION |
CASCADE}] [ON UPDATE {NO ACTION | CASCADE}]

The default is
NO
ACTION for all three
implementations.

The following examples illustrate the
difference between
NO
ACTION and
CASCADE using Oracle
9i syntax.

Listing 4-1 uses previously created
SALESMAN and
ADDRESS tables with default (NO
ACTION) option on the referential
integrity constraint
FK_ADDR_SALESMAN.

Listing 4-1:
Default (NO ACTION) option

SQL> INSERT INTO
salesman 2 ( 3 salesman_id_n, 4 salesman_code_s, 5 salesman_name_s, 6
salesman_status_s 7 ) 8 VALUES 9 ( 10 23, 11 '02', 12 'FAIRFIELD BUGS
ASSOCIATION', 13 'Y' 14 ); 1 row created. SQL> INSERT INTO address 2 ( 3
addr_id_n, 4 addr_custid_fn, 5 addr_salesmanid_fn, 6 addr_address_s, 7
addr_type_s, 8 addr_city_s, 9 addr_state_s, 10 addr_zip_s, 11 addr_country_s 12
) 13 VALUES 14 ( 15 49, 16 NULL, 17 23, 18 '223 E FLAGLER ST.', 19 NULL, 20
'MIAMI', 21 'FL', 22 '33131', 23 'USA' 24
); 1 row created. SQL> commit; Commit complete. SQL> DELETE FROM
salesman; DELETE FROM salesman * ERROR at line 1: ORA-02292: integrity
constraint (TEMP.FK_ADDR_SALESMAN) violated - child record
found

Listing 4-2 — with
CASCADE option on the referential
integrity constraint
FK_ADDR_SALESMAN:

Listing 4-2: CASCADE option

SQL> drop table
ADDRESS; Table dropped. SQL> CREATE TABLE address 2 ( 3 addr_id_n INT
CONSTRAINT pk_addrprimary PRIMARY KEY, 4 addr_custid_fn INT, 5
addr_salesmanid_fn INT CONSTRAINT fk_addr_salesman 6 REFERENCES salesman
(salesman_id_n) 7 ON DELETE CASCADE, 8 addr_address_s VARCHAR(60), 9
addr_type_s VARCHAR(8) CONSTRAINT chk_addr_type CHECK 10 (addr_type_s IN
('BILLING', 'SHIPPING')), 11 addr_city_s VARCHAR(18) CONSTRAINT nn_addr_city
NOT NULL, 12 addr_state_s CHAR(2), 13 addr_zip_s VARCHAR(10) NOT NULL, 14
addr_country_s CHAR(3) 15 ); Table created. SQL> INSERT INTO address 2 ( 3
addr_id_n, 4 addr_custid_fn, 5 addr_salesmanid_fn, 6 addr_address_s, 7
addr_type_s, 8 addr_city_s, 9 addr_state_s, 10 addr_zip_s, 11 addr_country_s 12
) 13 VALUES 14 ( 15 49, 16 NULL, 17 23, 18 '223 E FLAGLER ST.', 19 NULL, 20
'MIAMI', 21 'FL', 22 '33131', 23 'USA' 24 ); 1 row created. SQL> COMMIT;
Commit complete. SQL> SELECT * FROM ADDRESS; ADDR_ID_N ADDR_CUSTID_FN
ADDR_SALESMANID_FN ADDR_ADDRESS_S ---------- -------------- ------------------
----------------- 49 23 223 E FLAGLER ST. SQL> DELETE FROM salesman; 1 row
deleted. SQL> SELECT * FROM address; no rows selected

Deferrable constraints

SQL99 standards say that constraints
can be either
DEFERRABLE or
NOT
DEFERRABLE (default). A
NOT
DEFERRABLE constraint is checked
after each DDL statement;
DEFERRABLE constraints can either be
checked immediately after every
INSERT,
DELETE, or
UPDATE (INITIALLY
IMMEDIATE) or at the end of the
transaction (INITIALLY
DEFERRED).

That feature can be especially
helpful when data loads are performed with no particular order; that allows you
to load data into child table(s) first, then into parent table(s). Another use
would be loading data that does not comply with a
CHECK constraint and then updating it
appropriately.

The only vendor out of our "big
three" who provides deferrable constraints is Oracle 9i.
The syntax is

[[NOT] DEFERRABLE
[INITIALLY {IMMEDIATE | DEFERRED}]]

or

[[INITIALLY {IMMEDIATE |
DEFERRED}] [NOT] DEFERRABLE]

Listing 4-3 illustrates the use of deferrable
constraints.

Listing 4-3: Using deferrable constraints

-- Create SALESMAN table
with NOT DEFERRABLE CHECK constraint -- chk_salesstatus on salesman_status_s
column SQL> DROP TABLE salesman; Table dropped. SQL> CREATE TABLE
salesman 2 ( 3 salesman_id_n NUMBER NOT NULL, 4 salesman_code_s VARCHAR2(2) NOT
NULL, 5 salesman_name_s VARCHAR2(50) NOT NULL, 6 salesman_status_s CHAR(1)
DEFAULT 'Y', 7 CONSTRAINT chk_salesstatus CHECK (salesman_status_s in ('N',
'Y')), 8 CONSTRAINT pk_salesmanprim PRIMARY KEY (salesman_id_n) 9 ); Table
created. -- Now, try to insert a row with salesman_status_s = 'A' SQL>
INSERT INTO salesman 2 ( 3
salesman_id_n, 4 salesman_code_s, 5 salesman_name_s, 6 salesman_status_s 7 ) 8
VALUES 9 ( 10 23, 11 '02', 12 'FAIRFIELD BUGS ASSOCIATION',  13
'A' 14 ); /* The result is a constraint violation error (constraint
is checked immediately). */ INSERT INTO SALESMAN * ERROR at line 1: ORA-02290:
check constraint (TEST.CHK_SALESSTATUS) violated -- Drop SALESMAN table and
re-create it with DEFERRABLE CHECK constraint SQL> DROP
TABLE salesman; Table dropped. SQL> CREATE TABLE salesman 2 ( 3
salesman_id_n NUMBER NOT NULL, 4 salesman_code_s VARCHAR2(2) NOT NULL, 5
salesman_name_s VARCHAR2(50) NOT NULL, 6 salesman_status_s CHAR(1) DEFAULT 'Y',
7 CONSTRAINT chk_salesstatus CHECK (salesman_status_s in ('N', 'Y')) 
8 DEFERRABLE INITIALLY DEFERRED, 9 CONSTRAINT pk_salesmanprim
PRIMARY KEY (salesman_id_n) 10 ); Table created. -- Try to insert the same row
again – works this time SQL> INSERT INTO salesman 2 ( 3 salesman_id_n, 4
salesman_code_s, 5 salesman_name_s, 6 salesman_status_s 7 ) 8 VALUES 9
(
10 23, 11 '02', 12 'FAIRFIELD BUGS ASSOCIATION', 13 'A' 14 ); 1 row created. --
Try to commit changes SQL> COMMIT; -- Error occurs – the constraint is
checked at the end of the transaction. COMMIT * ERROR at line 1: ORA-02091:
transaction rolled back ORA-02290: check constraint (TEST.CHK_SALESSTATUS)
violated -- Trying to insert again SQL> INSERT INTO salesman 2 ( 3
salesman_id_n, 4 salesman_code_s, 5 salesman_name_s, 6 salesman_status_s 7 ) 8
VALUES 9 ( 10 23, 11 '02', 12 'FAIRFIELD BUGS ASSOCIATION', 13 'A' 14 ); 1 row
created. -- Now update it with an appropriate value ('Y') SQL> UPDATE
salesman 2 SET salesman_status_s = 'Y' 3 WHERE salesman_status_s = 'A'; 1 row
updated. -- COMMIT works this time. SQL> COMMIT;

Commit complete.

The
INITIALLY
DEFERRED constraint status is
usually used for special cases (data loads, conversions, etc.); when used in
everyday database operations, the deferred constraints can be confusing and
even harmful.

For example, imagine someone issued
a couple dozen DML statements and then tries to commit their changes, but the
commit fails because the very first
INSERT statement violates an
INITIALLY
DEFERRED constraint. The
transaction will be rolled back, so the user would have to retype all the
statements. If the constraint were checked after each statement, the user would
have known to correct data before going any further.

The solution is simple: only use
INITIALLY
DEFERRED constraint status for
special needs and change it to
INITIALLY
IMMEDIATE whenever the deferred
functionality is no longer desirable. (You can always change it back and forth
if your constraint was created as
DEFERRABLE.) We'll learn more about
that in
Chapter
5.

ON COMMIT clause

This clause can be used for temporary
tables only. It specifies if the table rows are implicitly deleted at the end
of a transaction or preserved until the session ends, so consequent
transactions can use the temporary table data.

The clause can be used within Oracle's
CREATE
TABLE statgment or DB2's
DECLARE
TABLE. See the previous examples in the
section about temporary tables.

Physical properties clause

Now, it's a little bit of a
simplification, but generally data is physically stored on a database server's
hard disk(s). The precise definition is beyond the scope of a book about SQL,
but we are going to cover the very basics to help you better understand the
creation of the database objects.

The implementations use quite diverse
approaches, but the idea is the same: to be able to separate different database
objects by type and, ideally, to put them on separate physical disks to speed
up database operations. For example, all table data would live on Disk1, all
table indexes on Disk2, and all LOBs would be placed on Disk3. The importance
of such an approach varies from vendor to vendor; many other factors, like
database size, workload, server quality, and so on can also play their
role.

This book assumes the ACME sample
database will be used primarily for educational purposes. We don't expect you
to use a real big server with multiple disks, so the physical storage has
rather theoretical significance for now.

Oracle
9i

Oracle uses
tablespaces (logical database structure explained later in
this chapter). You can specify separate tablespaces for table data, table
indexes, and table LOBs:

CREATE
TABLE phone ( phone_id_n NUMBER CONSTRAINT pk_phonerimary PRIMARY KEY
USING INDEX  TABLESPACE INDEX01,
phone_custid_fn NUMBER, phone_salesmanid_fn NUMBER, phone_phonenum_s
VARCHAR2(20), phone_type_s VARCHAR2(20), CONSTRAINT chk_phone_type CHECK
(phone_type_s IN ('PHONE', 'FAX')) ) TABLESPACE DATA01

This example assumes the existence of
tablespaces
DATA01, where the table data will
reside, and
INDEX01, to store the index for the
primary key.

DB2 UDB 8.1

DB2 also uses tablespaces much as
Oracle does. You can specify separate tablespaces for table data, indexes, and
large objects:

CREATE TABLE phone (
phone_id_n INTEGER NOT NULL, phone_custid_fn INTEGER, phone_salesmanid_fn
INTEGER, phone_phonenum_s VARCHAR(20), phone_type_s VARCHAR(20), CONSTRAINT
chk_phone_type CHECK (phone_type_s IN ('PHONE', 'FAX')), CONSTRAINT
pk_phonerimary PRIMARY KEY (phone_id_n) ) IN
USERDATA01

The system-managed tablespace
USERDATA01 presumably exists. We'll
show how to create it at the end of the chapter in the section about
tablespaces.

The ability to specify where data
is physically stored on the server is just one of many options you can specify
when creating a database table. For example, you can specify how much space
will be left for updates when a row is inserted, regulate the physical table
growth, slice the table based on certain conditions (either horizontally or
vertically), place the different partitions on separate physical devices, and
so on.

Oracle also allows you to create
index-organized tables. An index-organized table is a special type of table in
which the table rows are maintained in an index built on the primary key. In
other words, unlike regular tables in which rows are stored in no particular
order, the rows of an index-organized table are always sorted by the primary
key field. The access to such tables based on primary key is therefore much
faster, but the DML statements can be considerably slower. For example, to
insert a row into an index-organized table, an RDBMS has to re-sort and rebuild
the whole physical table structure.

This syntax creates an
index-organized table named
ORDER_LINE:

CREATE TABLE ORDER_LINE (
ORDLINE_ID_N NUMBER NOT NULL, ORDLINE_ORDHDRID_FN NUMBER NOT NULL,
ORDLINE_PRODID_FN NUMBER, ORDLINE_ORDQTY_N NUMBER, ORDLINE_SHIPQTY_N NUMBER,
ORDLINE_CREATEDATE_D DATE, ORDLINE_CREATEDBY_S VARCHAR2(10), CONSTRAINT
PK_ORDLINEPRIM PRIMARY KEY (ORDLINE_ID_N) ) ORGANIZATION INDEX
;

MS SQL Server implements a similar
concept in clustered indexes, which are explained later in
this chapter.

MS SQL Server 2000

Instead of tablespaces, MS SQL Server
employs filegroups. Again, the idea is very similar; the
difference is mostly in the syntax:

CREATE TABLE phone (
phone_id_n INTEGER NOT NULL, phone_custid_fn INTEGER, phone_salesmanid_fn
INTEGER, phone_phonenum_s VARCHAR(20), phone_type_s VARCHAR(20), CONSTRAINT
chk_phone_type CHECK (phone_type_s IN ('PHONE', 'FAX')), CONSTRAINT
pk_phonerimary PRIMARY KEY (phone_id_n) ON INDEX01 )
ON DATA01

In this example, we have presumed the
existence of previously created filegroups
DATA01 and
INDEX01. MS SQL filegroups are
covered in more detail later in this chapter.

Identity clause

Sometimes in your database, you want to
generate unique sequential values, for example for a primary key column, for
order or invoice numbers, customer IDs, and so on. We already mentioned the
concept of identity columns in
Chapter
3; now we are going to cover it in greater detail.

Oracle
9i

Oracle does not have identity
columns; it uses special database objects called sequences
instead. You can simply create a table with a numeric field to be populated; at
the moment of table creation the RDBMS does not need to know that, for example,
you intend to populate the
PAYTERMS_ID_N field of the
PAYMENT_TERMS table using a sequence:

CREATE TABLE payment_terms
( payterms_id_n NUMBER NOT NULL, payterms_code_s VARCHAR(6), payterms_desc_s
VARCHAR(60), payterms_discpct_n NUMBER, payterms_daystopay_N NUMBER, CONSTRAINT
pk_payterms PRIMARY KEY (payterms_id_n) )

More about sequences can be found in
the
CREATE
SEQUENCE section that follows in this
chapter as well as in the
ALTER
SEQUENCE, and
DROP
SEQUENCE sections of
Chapter
5.

DB2 UDB 8.1

Identity properties for a column can
be specified instead of the default clause. You can
specify the starting value, the increment, the minimum and maximum values,
whether the sequence should cycle around when it reaches the maximum value or
whether it should stop, and the number of values cached in memory:

CREATE
TABLE payment_terms ( payterms_id_n INTEGER NOT NULL GENERATED ALWAYS
AS IDENTITY  (START WITH 1, INCREMENT BY 1, CACHE
5), payterms_code_s VARCHAR(6), payterms_desc_s VARCHAR(60),
payterms_discpct_n DECIMAL(5,2), payterms_daystopay_N INTEGER, CONSTRAINT
pk_payterms PRIMARY KEY (payterms_id_n) )

The default number of values to cache
is 20; you can specify
NO
CACHE if you don't want to cache
values.

MS SQL Server 2000

An identity column is created in a
very similar way with slightly different syntax:

CREATE
TABLE payment_terms ( -- The first 1 means "start with"; the second stands for
"increment by" payterms_id_n INT NOT NULL IDENTITY (1,1),
payterms_code_s VARCHAR(6), payterms_desc_s VARCHAR(60), payterms_discpct_n
DECIMAL(5,2), payterms_daystopay_n INT, CONSTRAINT pk_payterms PRIMARY KEY
(payterms_id_n) )

The caching option is not available
in MS SQL Server.

Creating new table as a copy of
another table

Sometimes it's very useful to be able
to create a table as a copy of another table. You can "clone" an existing table
by creating its exact copy (with or without data) in all "big three" databases
with slightly different syntax.

Oracle
9i

The statement below creates a
temporary table that is a copy of the
PAYMENT_TERMS table (including all
rows):

CREATE GLOBAL TEMPORARY
TABLE payment_terms2 AS (SELECT * FROM payment_terms);

The proper use of the
SELECT statement enables you to
create a table that only contains certain columns and/or rows from the target
table:

CREATE
TABLE customer ( cust_id_n NUMBER NOT NULL, cust_paytermsid_fn NUMBER,
cust_salesmanid_fn NUMBER, cust_status_s VARCHAR2(1) DEFAULT 'Y' NOT NULL,
cust_name_s VARCHAR2(50) NOT NULL, cust_alias_s VARCHAR2(15), cust_credhold_s
VARCHAR2(1) DEFAULT 'Y' NOT NULL, CONSTRAINT chk_cust_status CHECK
(cust_status_s IN ('N', 'Y')), CONSTRAINT chk_cust_credhold CHECK
(cust_credhold_s IN ('N', 'Y')), CONSTRAINT pk_custprimary PRIMARY KEY
(cust_id_n) ); CREATE TABLE phone ( phone_id_n NUMBER NOT NULL, phone_custid_fn
NUMBER, phone_salesmanid_fn NUMBER, phone_phonenum_s VARCHAR2(20), phone_type_s
VARCHAR2(20), CONSTRAINT chk_phone_type CHECK (phone_type_s IN ('PHONE',
'FAX')), CONSTRAINT pk_phonerimary PRIMARY KEY (phone_id_n) ); CREATE TABLE
customer_phone AS ( SELECT cust_name_s, phone_phonenum_s FROM customer, phone
WHERE cust_id_n = phone_custid_fn );

To create an empty table in Oracle,
deliberately use a
FALSE condition in the
WHERE clause:

/* We
know that all primary keys in ACME database are positive integers so we know
that PAYTERMS_ID_N < 0 condition always evaluates to "FALSE" */ CREATE
GLOBAL TEMPORARY TABLE payment_terms3 AS (SELECT * FROM payment_terms WHERE
PAYTERMS_ID_N < 0);

DB2 UDB 8.1

Either one of two statements below
will create a copy of the
PAYMENT_TERMS table in DB2 UDB:

CREATE TABLE payment_terms2
AS (SELECT * FROM payment_terms) DEFINITION ONLY 

CREATE TABLE payment_terms2
LIKE payment_terms

The first syntax (DB2 considers it a
special case of a summary table — see the sidebar "Creating Summary
Tables") is more flexible because it allows you to create tables based
on a subset of the original table columns or even on a multitable query:

CREATE
TABLE customer ( cust_id_n NUMBER NOT NULL, cust_paytermsid_fn NUMBER,
cust_salesmanid_fn NUMBER, cust_status_s VARCHAR2(1) DEFAULT 'Y' NOT NULL,
cust_name_s VARCHAR2(50) NOT NULL, cust_alias_s VARCHAR2(15), cust_credhold_s
VARCHAR2(1) DEFAULT 'Y' NOT NULL, CONSTRAINT chk_cust_status CHECK
(cust_status_s IN ('N', 'Y')), CONSTRAINT chk_cust_credhold CHECK
(cust_credhold_s IN ('N', 'Y')), CONSTRAINT pk_custprimary PRIMARY KEY
(cust_id_n) ) CREATE TABLE phone ( phone_id_n NUMBER NOT NULL, phone_custid_fn
NUMBER, phone_salesmanid_fn NUMBER, phone_phonenum_s VARCHAR2(20), phone_type_s
VARCHAR2(20), CONSTRAINT chk_phone_type CHECK (phone_type_s IN ('PHONE',
'FAX')), CONSTRAINT pk_phonerimary PRIMARY KEY (phone_id_n) ) CREATE TABLE
customer_phone AS ( SELECT cust_name_s, phone_phonenum_s FROM customer, phone
WHERE cust_id_n = phone_custid_fn ) DEFINITION ONLY -- required
clause

The foregoing statement creates an
empty
CUSTOMER_PHONE table with two
columns,
CUST_NAME_S and
PHONE_PHONENUM_S, with the same data
types as the corresponding columns in the underlying tables; you could have
achieved the same result using this syntax:

CREATE TABLE customer_phone
AS ( cust_name_s VARCHAR(50) NOT NULL, phone_phonenum_s VARCHAR(20)
)

The advantage of
CREATE
TABLE
...
LIKE syntax is that it can optionally
create a copy of table with all column defaults and/or identity columns.

MS SQL Server 2000

In MS SQL Server, you can create a
copy of the
PAYMENT_TERMS table using this
syntax:

SELECT * INTO
#PAYMENT_TERMS2 FROM PAYMENT_TERMS

Some RDBMS tables can be very
large, so querying them can take a long time. Even when all appropriate indexes
are in place and the database is properly tuned, some queries still have to
perform full table scans, in which each and every record is searched for
specific values, to return the desired results. That is especially true when
you want a query to retrieve records summarized with a
GROUP
BY clause using aggregate
functions.

The concept of summary tables
introduced in DB2 allows you to maintain a summary of table (or multiple
tables) data in another table, refreshing it every time when the underlying
table(s) data changes. The most interesting fact about DB2 summary tables is
that not only users can query them to obtain results faster, but the RDBMS
optimizer can also use them to execute users' queries that
indirectly request information already summarized in the summary tables.

Keep in mind that this only works
well on tables that are more or less static; if the source tables change
frequently (i.e., many DDL statements run against them), the overhead may
become too big and the overall performance may actually degrade.

You can have two types of summary
tables in DB2. Tables created with the
REFRESH
DEFERRED clause can be refreshed on
demand with the
REFRESH
TABLE
table_name statement; if the
REFRESH
IMMEDIATE clause was used, the
summary table will delete and reinsert rows automatically every time the
table(s) used in its
WHERE clause change. In both cases,
you have to use the
REFRESH
TABLE
... statement at least once on a
summary table before you (or the optimizer) can start using it.

Summary tables are subject to many
limitations. For example, you cannot use
ORDER
BY, outer joins, or
nondeterministic functions in the
select_statement; you must have
GROUP
BY clause and
COUNT
(*) column; all columns must be
named, and so on. See DB2 UDB documentation for all rules and
restrictions.

Here is an example of the table
ORDERLINE_SUMMARY that summarizes
ordered and shipped product quantities by order on demand:

CREATE TABLE
orderline_summary AS ( SELECT ordline_ordhdrid_fn, SUM(ordline_ordqty_n) AS
ord_qty_summary, SUM(ordline_shipqty_n) AS ship_qty_summary, COUNT (*) AS
rowcount FROM order_line GROUP BY ordline_ordhdrid_fn ) DATA INITIALLY DEFERRED
REFRESH DEFERRED

Oracle provides similar
capabilities by using MATERIALIZED VIEWS discussed latter in this
chapter.