character sets and collating orders...

Pokaż mi serce nie opętane zwodniczymi marzeniami, a pokażę ci człowieka szczęśliwego.

CREATE TABLE BOOKADVANCE (BOOKNO CHAR(6),
TITLE CHAR(50) CHARACTER SET DOS437 COLLATE PDOX_INTL,
EUROPUB CHAR(50) CHARACTER SET ISO8859_1 COLLATE FR_FR);
For a list of the available characters sets and collation orders that InterBase recognizes,
see Chapter 14, “Character Sets and Collation Orders.”
4 Defining domain-based columns
When you create a table, you can set column attributes by using an existing domain
definition that has been previously stored in the database. A domain is a global column definition. Domains must be created with the CREATE DOMAIN statement before you can
reference them to define columns locally. For information on how to create a domain, see
Chapter 5, “Working with Domains.”
Domain-based columns inherit all the characteristics of a domain, but the column
definition can include a new default value, additional CHECK constraints, or a collation
clause that overrides the domain definition. It can also include additional column
constraints. You can specify a NOT NULL setting if the domain does not already define one.
94
INTERBASE 5
CREATING TABLES
Note You cannot override the domain’s NOT NULL setting with a local column definition.
For example, the following statement creates a table, COUNTRY, referencing the domain,
COUNTRYNAME, which was previously defined with a datatype of VARCHAR(15):
CREATE TABLE COUNTRY
(COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
CURRENCY VARCHAR(10) NOT NULL);
4 Defining expression-based columns
A computed column is one whose value is calculated each time the column is accessed
at run time. The syntax is:
<col_name> COMPUTED [BY] (<expr>);
If you do not specify the datatype, InterBase calculates an appropriate one. expr is any arithmetic expression that is valid for the datatypes in the columns; it must return a single value, and cannot be an array or return an array. Columns referenced in the expression
must exist before the COMPUTED [BY] clause can be defined.
For example, the following statement creates a computed column, FULL_NAME, by
concatenating the LAST_NAME and FIRST_NAME columns.
CREATE TABLE EMPLOYEE
(FIRST_NAME VARCHAR(10) NOT NULL,
LAST_NAME VARCHAR(15) NOT NULL,
FULL_NAME COMPUTED BY (LAST_NAME || ", " || FIRST_NAME));
The next example creates a table with a calculated column (NEW_SALARY) using the
previously created EMPNO and SALARY domains.
CREATE TABLE SALARY_HISTORY
(EMP_NO EMPNO NOT NULL,
CHANGE_DATE DATE DEFAULT "NOW" NOT NULL,
UPDATER_ID VARCHAR(20) NOT NULL,
OLD_SALARY SALARY NOT NULL,
PERCENT_CHANGE DOUBLE PRECISION
DEFAULT 0
NOT NULL
CHECK (PERCENT_CHANGE BETWEENoreign key
50 AND 50),
NEW_SALARY COMPUTED BY
(OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),
PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID),
FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO)
ON UPDATE CASCADE
DATA DEFINITION GUIDE
95
CHAPTER 6 WORKING WITH TABLES
ON DELETE CASCADE);
Note Constraints on computed columns are not enforced, but InterBase does not return
an error if you do define such a constraint.
4 Specifying column default values
You can set an optional default value that is automatically entered into a column if you
do not specify an explicit value. Defaults set at the column level with CREATE TABLE or
ALTER TABLE override defaults set at the domain level. Defaults can save data entry time
and prevent data entry errors. For example, a possible default for a DATE column could
be today’s date, or in a (Y/N) flag column for saving changes, “Y” could be the default.
Default values can be:
g literal—The default value is a user-specified string, numeric value, or date value.
g NULL—If the user does not enter a value, a NULL value is entered into the column.
g USER—The default is the name of the current user. If your operating system supports the
use of 8 or 16-bit characters in user names, then the column into which USER will be
stored must be defined using a compatible character set.
In the following example, the first statement creates a domain with USER named as the
default. The next statement creates a table that includes a column, ENTERED_BY, based on
the USERNAME domain.
CREATE DOMAIN USERNAME AS VARCHAR(20)
DEFAULT USER;
CREATE TABLE ORDERS (ORDER_DATE DATE, ENTERED_BY USERNAME, ORDER_AMT
DECIMAL(8,2));
INSERT INTO ORDERS (ORDER_DATE, ORDER_AMT)
VALUES ("1-MAY-93", 512.36);
The INSERT statement does not include a value for the ENTERED_BY column, so InterBase
automatically inserts the user name of the current user, JSMITH:
SELECT * FROM ORDERS;
4 Specifying NOT NULL
You can optionally specify NOT NULL to force the user to enter a value. If you do not