Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 130 131 132 133 134 135 < 136 > 137 138 139 140 141 142 .. 241 >> Next


Sex CHAR UPPERCASE NOT NULL CHECK (Sex IN ('M','F')),

Race CHAR UPPERCASE,

MStat CHAR UPPERCASE CHECK (MStat IN ('S','M','D','U')), EdLev BYTEINT FORMAT 'Z9' CHECK (EdLev >=0 AND EdLev <=22) NOT NULL,

HCap BYTEINT FORMAT 'Z9' CHECK (HCap >= -99 AND HCap <= 99 ) UNIQUE PRIMARY INDEX (EmpNo),

INDEX (Name) ;

In this example, a unique primary index is defined on the EmpNo column, and a unique secondary index is defined on the Name column. Because the EmpNo and Name fields must always contain a value, they are assigned the NOT NULL attribute.

For display purposes, the values for Sex, Race, and MStat are defined as UPPERCASE. The Name column is not defined as a unique index because it is possible for two or more employees to have the same name.

Teradata RDBMS for UNIX SQL Reference

8-89
Teradata SQL Syntax Guide

CREATE TABLE

Example 2: Use of DATABLOCKSIZE and FREESPACE

CHECK (EmpNo >= 10001 AND EmpNo

999' CHECK (DeptNo>= 100 AND DeptNo

The following example creates the same Employee table as in the above example but includes options to specify a maximum data block size of 16384 bytes (32 sectors) and 10% freespace:

CREATE TABLE Employee,

DATABLOCKSIZE = 16384 BYTES,

FREESPACE = 10 PERCENT,

(EmpNo SMALLINT FORMAT '9(5)'

<= 32001)

NOT NULL,

Name VARCHAR(12) NOT NULL,

DeptNo SMALLINT FORMAT <=900),

JobTitle VARCHAR(12),

Salary DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99'

CHECK (Salary >= 1.00 AND Salary <= 999000.00),

YrsExp BYTEINT FORMAT 'Z9' CHECK (YrsExp >= -99 AND YrsExp <=99),

dob DATE FORMAT 'MMMbDDbYYYY' NOT NULL,

Sex CHAR UPPERCASE NOT NULL,

Race CHAR UPPERCASE,

MStat CHAR UPPERCASE,

EdLev BYTEINT FORMAT 'Z9' CHECK(EdLev >=0 AND EdLev <= 22) NOT NULL,

HCap BYTEINT FORMAT 'Z9' CHECK (BCap >= -99 AND HCap <= 99 ) UNIQUE PRIMARY INDEX (EmpNo),

INDEX (Name) ;

Example 3: Referential Constraints

Here, Table Tl is created with referential constraints which specify:

For a table level constraint: that Foreign Key columns (fl, f2) reference the unique primary index columns in table T2.

For a column level constraint: that Foreign Key columns (fl, f2) reference specifically column f1 of table T3.

CREATE TABLE T1

(f1 integer NOT NULL REFERENCES t3(f1), f2 integer)

PRIMARY INDEX (f1)

FOREIGN KEY (f1, f2) REFERENCES T2 ;

Example 4: Named Constraints - Column

Level

The statement in this example names constraints at the column level.

CREATE TABLE good_1 ( field_1 INTEGER NOT NULL

CONSTRAINT primary_1 PRIMARY KEY, field_2 INTEGER NOT NULL

CONSTRAINT unique_1 UNIQUE, field_3 INTEGER

CONSTRAINT check_1 CHECK (field_3 > 0), field_4 INTEGER

CONSTRAINT reference_1 REFERENCES parent_1

8-90

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE TABLE

Example 5: Named Constraints - Table Level

Example 6: Named and Unnamed Constraints -Table and Column Levels

The statement in this example names constraints at the table level.

CREATE TABLE good_2 ( field_1 INTEGER NOT NULL field_2 INTEGER NOT NULL, field_3 INTEGER NOT NULL, field_4 INTEGER NOT NULL, field_5 INTEGER, field_6 INTEGER,

CONSTRAINT primary_1

PRIMARY KEY (field_1, field_2),

CONSTRAINT unique_1

UNIQUE (field_3, field_4),

CONSTRAINT check_1

CHECK (field_3 > 0 OR field_4 IS NOT NULL),

CONSTRAINT reference_1

FOREIGN KEY (field_5, field_6)

REFERENCES parent_1 (field_2, field_3)

)

The statement in this example defines named and unnamed constraints at both the column and table levels.

CREATE TABLE good_3 ( field_1 INTEGER NOT NULL CONSTRAINT primary_1 PRIMARY KEY, field_2 INTEGER NOT NULL CONSTRAINT unique_1 UNIQUE CONSTRAINT check_1 CHECK (field_2 <> 3)

CONSTRAINT reference____1

REFERENCES parent_1 CHECK (field_2 > 0)

REFERENCES parent_1 (field_4), field_3 INTEGER NOT NULL field_4 INTEGER NOT NULL field_5 INTEGER,

CONSTRAINT unique_2 UNIQUE (field_3),

CONSTRAINT check_2

CHECK (field_3 > 0 AND field_3 < 100),

CONSTRAINT reference_2 FOREIGN KEY (field_3)

REFERENCES parent_1 (field_5),

UNIQUE (field_4),

CHECK (field_4 > field_5),

FOREIGN KEY (field_4, field_5)

REFERENCES parent_1 (field_6, fielt_7)

)

Teradata RDBMS for UNIX SQL Reference

8-91
Teradata SQL Syntax Guide

CREATE TABLE

Example 7: Named and Unnamed CHECK Constraints at Both Levels

Example 8: Primary Index and Primary Key Specification

Example 9: Primary Key Specification

Example 10: Neither Primary Index Nor Primary Key Are Specified

The statement in this example combines the three unnamed CHECKs for field_1. Constraint check_0 and each of the named CHECKs for field_2 are treated as Table Constraints.

CREATE TABLE good_4 ( field_1 INTEGER

CHECK (field_1 > 0)

CHECK (field_1 < 999)

CHECK (field_1 NOT IN (100,200,300)

CONSTRAINT check_0

CHECK (field_1 IS NOT NULL), field_2 INTEGER

CONSTRAINT check_1 CHECK (field_2 > 0)

CHECK (field_2 < 999)
Previous << 1 .. 130 131 132 133 134 135 < 136 > 137 138 139 140 141 142 .. 241 >> Next