Database Reference
In-Depth Information
Now we can create the ARTISTS table again, but this time we add a few
more columns and some check constraints into the script.
CREATE TABLE ARTISTS(
ARTIST_ID NUMBER PRIMARY KEY
, NAME VARCHAR2(32) NOT NULL, CITY VARCHAR2(32)
, STATE_PROVINCE VARCHAR2(32) CONSTRAINT STATE
CHECK
(LENGTH(STATE_PROVINCE) >= 2 OR STATE_PROVINCE IS NULL)
, COUNTRY VARCHAR2(32) CONSTRAINT COUNTRY
CHECK
(COUNTRY IN('USA','Canada','England','Australia'))
, MEDIA_ROYALTIES FLOAT, RECORD_SALES_ROYALTIES FLOAT
, PERFORMANCE_PROFITS FLOAT
, CONSTRAINT ROYALTIES
CHECK
(MEDIA_ROYALTIES
+ RECORD_SALES_ROYALTIES + PERFORMANCE_PROFITS > 0));
The constraints created in the ARTISTS table are as follows:
An inline primary key constraint on the ARTIST_ID column.
An inline NOT NULL constraint on the NAME column.
An inline CHECK constraint named STATE that checks the length
of the STATE_PROVINCE column. If the length of the value is
greater than or equal to 2, or if the value is NULL, the value passes
the test.
An inline CHECK constraint named COUNTRY that looks for cer-
tain values. If the value in the COUNTRY column is in the list of
four countries provided, the value passes; otherwise it fails.
An out-of-line CHECK constraint named ROYALTIES that looks at
an expression. If the sum of MEDIA_ROYALTIES,
RECORD_SALES_ROYALTIES, and PERFORMANCE_PROFITS
is greater than zero, the row passes the test; otherwise it fails.
Now we want to show the use of the CHECK constraints added to the
newly created ARTISTS table. Two of the following five INSERT com-
mands will add a row. The other three will produce an error. See the output
in Figure 20.5. Invalid values (if any) in each of the INSERT commands are
highlighted.
