Oracle Creation Table statement (CREATE TABLE) syntax detailed and examples

Creating a table (CREATE TABLE) syntax detailed
1. Types of fields commonly used by Oracle
Oracle's Common field types are
VARCHAR2 (size) variable-length string, which must be specified in length
CHAR (size) fixed-length string, not specified length default value is 1
Number (p,s) digit p is the total length of the digits, S is the length of the decimal, can be negative
The maximum 38 bits. When not enough bits are rounded.
Date and Time type
LOB Extra long characters up to 4G
CLOB Extra Long text string
BLOB Extra-Long binary string
BFILE Extra-long binary strings, which are read-only in files that are stored outside the database.
Number field type bits and results of rounding
Original Value 1234567.89
Number field type number of bits stored value
Number 1234567.89
Number (8) 12345678
Number (6) wrong
Number (9,1) 1234567.9
Number (9,3) wrong
Number (7,2) wrong
Number (5,-2) 1234600
Number (5,-4) 1230000
Number (*,1) 1234567.9
2. Add default values and constraints to a field when creating a table
You can add a default value to a field when you create a table
Example: Date field DEFAULT sysdate
In this way, each time you insert and modify the field, you do not have to use the program to get the action.
You can add constraints to a field when you create a table
For example: non-null NOT NULL
Duplicate unique is not allowed
Keyword PRIMARY key
Checking check (condition) by condition
Foreign key REFERENCES table name (field name)
3. Example of creating a table
CREATE TABLE DEPT (
Eptno Number (2) CONSTRAINT pk_dept PRIMARY KEY,
Dname VARCHAR2 (14),
LOC VARCHAR2 (13));
CREATE TABLE Region (
ID Number (2) not NULL PRIMARY KEY,
Postcode number (6) Default ' 0 ' not NULL,
AreaName varchar2 (+) Default ' not NULL ';
4. Naming rules and considerations when creating a table
1) Naming rules for table names and field names: Must start with a letter and can contain symbols a-z,a-z,0-9,_,$,#
2) Case insensitive
3) do not use the reserved word in SQL, be sure to use double quotation marks to enclose the string.
4) There are certain restrictions on the length of the English symbols associated with the entity or attribute
Precautions:
1) You can use the Chinese field name when building the table, but it is best to use the field name in English
2) When creating a table, place the smaller, non-empty fields in front, and possibly empty fields behind
3) When a table is built with a unique keyword or a unique constraint, the index is built automatically when the table is built
4) The maximum number of fields for a table is also limited, and 254.
5. Naming rules and syntax for constraint names
Naming rule constraint name for constraint name if not specified in the table, the system naming convention is SYS_CN (n is a number)
Naming conventions for constraint name strings with Yu Quai and field names
6. Considerations when working with constraints
Constraint cannot use system functions, such as sysdate and other table field comparisons
You can use the comparison of the fields in this table
I want to do the constraint after the transaction.

Check
Sql> ALTER session set constraints deferred.
7. Examples of entity diagrams to create tablesS_dept
Prerequisites: Field IDs with unique keywords already in the region table
sql> CREATE TABLE s_dept
(ID number (7)
CONSTRAINT s_dept_id_pk PRIMARY KEY,
Name VARCHAR2 (25)
CONSTRAINT S_dept_name_nn not NULL,
region_id Number (7)
CONSTRAINT S_DEPT_REGION_ID_FK REFERENCES Region (ID),
CONSTRAINT S_dept_name_region_id_uk UNIQUE (name, region_id));
8. More complex examples of creating tables
sql> CREATE TABLE s_emp
(ID number (7)
CONSTRAINT s_emp_id_pk PRIMARY KEY,
Last_Name VARCHAR2 (25)
CONSTRAINT S_emp_last_name_nn not NULL,
First_Name VARCHAR2 (25),
UserID VARCHAR2 (8)
CONSTRAINT S_emp_userid_nn not NULL
CONSTRAINT S_emp_userid_uk UNIQUE,
Start_date Date DEFAULT sysdate,
Comments VARCHAR2 (25),
MANAGER_ID Number (7),
Title VARCHAR2 (25),
DEPT_ID Number (7)
CONSTRAINT S_EMP_DEPT_ID_FK REFERENCES s_dept (ID),
Salary number (11,2),
commission_pct Number (4,2)
CONSTRAINT S_emp_commission_pct_ck CHECK
(commission_pct in (10,12.5,15,17.5,20)));
9. Create a table from a subquery
Example of building a table through a subquery
Sql>create TABLE emp_41 as SELECT ID, last_name, userid, start_date
From s_emp WHERE dept_id = 41;
Sql> CREATE TABLE A as SELECT * from B where 1=2;
As long as the table is structured.
10. Considerations for using sub-queries to build tables
1) You can close multiple tables and generate new tables with aggregate functions, noting that the selected field must have a valid field name and cannot be duplicated.
2) The table created by the subquery, only the non-null NOT NULL constraints can inherit, the other constraints and default values are not inherited.
3) as required, you can use ALTER TABLE add Constraint ... Other constraints, such as primary key, are established.
One . Foreign key Optional parameters on DELETE CASCADE
You can add optional parameters when creating foreign key:
On DELETE Cascade It means that if you delete the contents of the Foreign key Main table, the contents of the child table will be deleted together.
If there is no on DELETE cascade argument, the child table has content and the parent table's primary key record cannot be deleted.
12. If there is an unsatisfied record in the database table, establishing the constraint will not succeed.
13. Examples of creating and deleting synonyms for a table
sql> CREATE synonym D_sum
2 for Dept_sum_vu;
sql> CREATE public synonym s_dept
2 for Alice.s_dept;
sql> DROP synonym s_dept;

Oracle Creation Table statement (CREATE TABLE) syntax detailed and examples