CREATE TABLE arrayname
(Col_name type (size) [NULL | NOT NULL]
column_name type (size) [NULL | NOT NULL]
, ...);
Option NULL (the default) means that the fields in this column may take NULL.
Option NOT NULL means that the fields in this column must have a specific value, can not accept NULL values.
Example
CREATE TABLE DEPT (
DEPTNO NUMBER NOT NULL,
DNAM VARCHAR2 (12));
DESCRIBE command
To see what tables have a column and what are the conditions imposed on them, write:
DESC [DESCRIBE] tbl_name;
To define other than NOT NULL conditions integrity CONSTRAINT clause is used. Conditions can be entered directly on the column definition, or at the end after defining all the columns.
The condition placed on the column definition:
CREATE TABLE arrayname
(...
column_name type (size)
Condition_type condition_name CONSTRAINT [condition],
, ...);
The condition placed on the definitions for each column:
CREATE TABLE arrayname
(...
column_name type (size),
...
CONSTRAINT condition_name condition_type condition
CONSTRAINT condition_name condition_type condition
, ...);
- condition_name - is the identifier of the condition of integrity, are not required to be administered, but then the system will self-condition, usually unreadable identifier. ID is required at the headquarters of integrating on and off conditions of integrity
- condition_type - one of the following: CHECK, PRIMARY KEY, UNIQUE, FOREIGN KEY.
- condition - additional information depending on the type of condition, if placed CONSTRAINT clause after the column definitions of the condition must always be defined.
CHECK condition
Specifies the condition to be fulfilled by the value in a column for each row inserted, the condition can not refer to other tables.
CONSTRAINT CHECK (logical condition);
Logical condition must be simple, do not use subqueries. Do not also use the feature, whose value depends on the circumstances of the call, such as SYSDATE or USER.
Example
CREATE TABLE emp
(...
ENAME VARCHAR2 (10) upper_name_ch CONSTRAINT CHECK (ENAME = UPPER (ENAME)),
, ...);
PRIMARY KEY Condition
Defines the primary key table. If there is one primary key column, the more convenient condition to save the definition of this column. If the primary key based on the number of columns it convenient to define the definition of all columns.
The definition of the condition of the column definition:
Type Size column PRIMARY KEY CONSTRAINT condition_name;
The definition of the condition after the definition of all columns:
CONSTRAINT condition_name
PRIMARY KEY (column_1, column_2, ...);
The table can be only one master key. All columns included in the master key is mandatory - no need to further impose the condition NOT NULL. For each line, a set of values for the primary key must be unique -
otherwise Oracle will report an error.
Example
CREATE TABLE emp
(Empno NUMBER (4) emp_pk CONSTRAINT PRIMARY KEY,
, ...);
or
CREATE TABLE emp
(Empno NUMBER (4),
...
Emp_pk CONSTRAINT PRIMARY KEY (empno),
, ...);
UNIQUE condition
Defines a unique key of the table. The key is similar to a unique primary key, the fact that it automatically enforces NOT NULL condition in their columns.
If the key is a unique column, convenient store after the definition of the condition of this column. If the key is based on a few columns to define it more convenient to the definition of all columns.
The definition of the condition of the column definition:
column type size nazwa_warunku UNIQUE CONSTRAINT
The definition of the condition after the definition of all columns:
Condition_name CONSTRAINT UNIQUE (column_1, column_2, ...)
The table may be a number of unique keys. For each line, a set of unique values for the key must be unique - otherwise Oracle will report an error. NOT NULL condition may be to add a unique key columns separately.
Example
CREATE TABLE dept
(DNAM VARCHAR2 (12) CONSTRAINT UNIQUE dept_uk
, ...);
FOREIGN KEY
Defines a foreign key, representing the relationship with another table. Makes, column values with this key can take only the values of primary key or unique other, indicated table.
If there is one foreign key column, convenient store after the definition of the condition of this column. If the key is based on a few columns to define it more convenient to the definition of all columns.
The definition of the condition of the column definition:
CONSTRAINT column type size condition_name
REFERENCES table-name-list;
The definition of the condition after the definition of all columns:
CONSTRAINT condition_name
FOREIGN KEY (list of columns defined)
REFERENCES table-name-list;
For the foreign key can be defined must be defined before the table, to which he refers is the key, and on a set of columns specified by the foreign key must be defined a primary key or unique. If we refer to a foreign master key table, you need not specify the column list of the key.
Properties of the foreign key
- The table with foreign key can not insert a row with foreign key values that do not have counterparts in the foreign table
- The table with foreign key can not modify the line in the foreign key values that do not have counterparts in the foreign table
- The foreign table can not delete the row is referenced in the foreign key value to another table. You can request removal of the line all the rows in a table with foreign key to which this row refers. For this purpose, the CONSTRAINT clause defining a foreign key must be placed ON DELETE CASCADE directive.
Example
The following table is emp-defined two foreign keys:
- DEPTNO - the DEPTNO table dept
- MA - empno column of the table emp and a request to remove all employees in the event of removal of the head
CREATE TABLE EMP (
Empno NUMBER (4) EMP_PRIMARY_KEY CONSTRAINT PRIMARY KEY,
ENAME VARCHAR (10),
JOB VARCHAR (9),
MGR NUMBER (4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (empno),
HireDate DATE,
SAL NUMBER (7,2),
COMM NUMBER (7,2),
DEPTNO NUMBER (2) NOT NULL
EMP_FOREIGN_KEY CONSTRAINT FOREIGN KEY REFERENCES DEPT
ON DELETE CASCADE);
DEFAULT clause
DEFAULT clause is used to indicate what value is to be inserted into the column, if not be a specific value.
...
column type (size) DEFAULT expression
...
The expression must be simple, do not use subqueries are allowed SYSDATE and USER functions.
No comments:
Post a Comment