Oracle_sql (4) DDL tables and constraints

Database objects are divided into objects that occupy storage space and objects that do not occupy storage space.
The objects that occupy the storage space mainly include: table, index and so on.
Select distinct Segment_type from dba_segments order by Segment_type;
The objects that do not occupy storage space include: views, sequences, functions, stored procedures, packages, triggers, and so on.
Select distinct object_type from dba_objects order by object_type;

DDL is used to create (create), replace, change (alter),
Delete (drop), rename (rename), truncate (TRUNCATE) The statement of the database object.
This section focuses on tables, indexes, views, sequences, functions, stored procedures, packages, triggers, and other objects that are explained in a follow-up plsql course.

I. Tables (table)
The tables in the database are similar to the tables we use in our daily lives,
It is also made up of rows and columns (column).
Columns are made up of homogeneous information, each column is also called a field, and the title of each column is called the field name.
The row includes several column information items.
A row of data is called one or a record, and it expresses a meaningful combination of information.
A database table consists of one or more records, and a table without records is called an empty table.
Each table typically has a primary key that uniquely identifies a record.
1. Example
View Dept;
CREATE TABLE DEPT
(
Deptno Number (2) is not NULL,
Dname VARCHAR2 (14),
Loc VARCHAR2 (13)
);
On this basis we create our student table student
CREATE TABLE Student
(
Sid Number (TEN) is not NULL,
Sname varchar2 (20),
Birth_date Date
);

2. CREATE TABLE syntax
CREATE TABLE Table name
(
Column name data type (length) constraint,
...
);

3. Data type
3.1 Numeric type number
The total length is 40 bits, one of which is used to denote a decimal point,
The maximum number of 38 bits.
Integer number (n)
The fractional number (M,n) m is the total length, n is the number of decimal places, (m-n) is the integer number of digits
3.2 Character Type varchar2
Variable long string with a total length of 4,000 bytes.
3.3 DateTime Type Date
Valid range January 1, 4712 BC, until December 31, 9999
3.4 Large Text Clob
A character large object containing single-byte characters.
Both Fixed-width and variable-width character sets are
Supported, both using the CHAR database character set.
Maximum size is 4 gigabytes.
3.5 Binary Large File blob
A binary Large object. Maximum size is 4 gigabytes.
3.6 Early numeric types
Long length shaping Character data of variable length up to 2 gigabytes, or 2^31-1 bytes.
3.7 Early character types
CHAR (size) fixed-length character data of length size bytes. Maximum size is bytes. Default and minimum size is 1 byte.
NCHAR (size) fixed-length character data of length size characters. Maximum size is determined by the national character set definition, with an upper limit of $ bytes. Default and minimum size is 1 character.
3.8 Early date types
TIMESTAMP year, month, and day values of date, as well as hour, minute, and second values of time
3.9 Early binary large files
Raw (size) raw binary data of length size bytes. Maximum size is bytes. You must specify size for a RAW value.
LONG Raw Raw binary data of variable length up to 2 gigabytes.

4. Constraints
4.1 Non-null constraint NOT NULL
The value of this column is not allowed to be empty when a record is added or modified
4.2 Check Constraint check (sal>0)
When a record is added or modified, the value of the column needs to meet the criteria of the check setting
4.3 PRIMARY KEY constraint primary key (comes with non-null constraints)
When a record is added or modified, the value of the column cannot be duplicated with that column value of another record
4.4 Unique key constraint unique (without a non-null constraint)
When a record is added or modified, the value of the column cannot be duplicated with that column value of another record
4.5 FOREIGN KEY Constraint references Dept (DEPTNO)
When you add or modify a record, the value of the column needs to exist in the parent table's primary key column
The difference between a primary KEY constraint and a unique constraint:
The primary KEY constraint comes with a non-null constraint, and the unique key constraint does not have a non-null constraint.
The primary key is usually sequence number, there is sequence generation, no practical meaning,
A unique key is generally a key attribute of an entity record and has practical significance.
The primary KEY constraint does not guarantee that the same entity in the real world can enter two repetitions in the system.
A unique key constraint guarantees that the same entity can be entered only once in the system.

5. More complete Table statements
CREATE TABLE Emp_4
(
Empno Number (4) primary key,
Ename VARCHAR2 (TEN) is not null unique,
Job VARCHAR2 (9),
Mgr Number (4),
HireDate DATE Default Sysdate,
Sal Number (7,2) default 0 NOT NULL check (sal>=0),
Comm Number (7,2),
Deptno Number (2) References DEPT (DEPTNO)
);
Insert into Emp4 (EMPNO) values (null);
Insert into Emp4 (empno) values (1);
Insert into Emp4 (EMPNO,ENAME,SAL,DEPTNO) VALUES (1, ' AAA ', 1000,10);
Insert into Emp4 (EMPNO,ENAME,SAL,DEPTNO) VALUES (1, ' AAA ', 1000,10);
Insert into Emp4 (EMPNO,ENAME,SAL,DEPTNO) VALUES (2, ' AAA ', 1000,10);
Insert into Emp4 (EMPNO,ENAME,SAL,DEPTNO) VALUES (2, ' AaB ', 1000,9);
Insert into Emp4 (EMPNO,ENAME,SAL,DEPTNO) VALUES (2, ' AaB ', -1000,10);
Insert into Emp4 (EMPNO,ENAME,SAL,DEPTNO) VALUES (2, ' AaB ', 1000,10);

6. Default value of column
When a record is added or modified, if the value of the column is NULL, the default value is assigned to the column.
Insert into Emp4 (EMPNO,ENAME,DEPTNO) VALUES (3, ' AAC ', 10);

7. Delete Table syntax
drop table name [cascade| RESTRICT];
drop table Emp4;
CASCADE Deleting a table will delete the data in the table, the related views and constraints of the table, etc.
RESTRICT The table cannot be deleted without a view or constraint referencing the table, the default is RESTRICT.
--Delete the Dept table and remove the EMP table from the foreign key of the Dept table
DROP TABLE dept Cascade constraints;

8. Table renaming
Syntax: ALTER TABLE name rename to new table name;
ALTER TABLE Emp4 Rename to Emp1;

9. Modify the table structure
9.1 Adding columns
Syntax: ALTER TABLE name Add (column list type column constraint);
ALTER TABLE EMP1 Add (phone number (11));
--alter table EMP1 Add (phone number (one), address varchar2 (100));
9.2 Deleting columns
ALTER TABLE name drop column name;
ALTER TABLE EMP1 drop column phone;
9.3 Modifying columns
Syntax: ALTER TABLE table name modify column list type;
ALTER TABLE EMP1 Modify phone number (15); --column type can be changed from small to large
ALTER TABLE EMP1 Modify phone number (9); --when there is no data, the column type can be changed from large to small
ALTER TABLE EMP1 modify phone NOT null; --Add non-null constraints to Columns
9.4 Adding a PRIMARY KEY constraint
/*create Table EMP2
(
Empno Number (4) is not NULL,
Ename VARCHAR2 (Ten) NOT NULL,
Job VARCHAR2 (9),
Mgr Number (4),
HireDate DATE,
Sal Number (7,2),
Comm Number (7,2),
Deptno Number (2)
); */
Syntax: ALTER TABLE name ADD CONSTRAINT constraint name primary key (column name,...);
ALTER TABLE EMP2 ADD constraint PK_EMP2 primary key (EMPNO);
9.5 Adding FOREIGN KEY constraints
Syntax: ALTER TABLE name ADD CONSTRAINT constraint name foreign key (column name,...)
References parent table name (column name,...);
ALTER TABLE EMP2 add constraint Fk_emp2_deptno
Foreign KEY (DEPTNO) references DEPT (DEPTNO);
9.6 Adding UNIQUE constraints
Syntax: ALTER TABLE name ADD CONSTRAINT constraint name unique (column name,...);
ALTER TABLE EMP2 add constraint uk_emp2 unique (ename);
9.7 Adding non-null constraints
ALTER TABLE EMP2 modify ename NOT null;
9.8 Adding CHECK constraints
Syntax: ALTER TABLE name ADD constraint constraint name check (expression);
ALTER TABLE EMP2 add constraint check_emp2_ename CHECK (ename is not null);
ALTER TABLE EMP2 add constraint check_emp2_sal CHECK (sal>=0);
9.9 constraint deletion, activation, and invalidation operations
Grammar:
ALTER TABLE name drop/disable/enable constraint constraint name;
Example:
ALTER TABLE EMP1 drop constraint check_emp1_sal;
ALTER TABLE EMP1 disable constraint fk_emp1_deptno;
ALTER TABLE EMP1 enable constraint Fk_emp1_deptno;

Modify syntax: ALTER TABLE <tab> MODIFY constraint constraint name [ENABLE | DISABLE] [VALIDATE | Novalidate];
ALTER TABLE EMP1 modify constraint check_emp1_ename disable;
ALTER TABLE EMP1 modify constraint fk_emp1_deptno disable;
ALTER TABLE EMP1 modify constraint pk_emp1 disable;
ALTER TABLE EMP1 modify constraint uk_emp1 disable;

10. Truncate (TRUNCATE) Table
Syntax: TRUNCATE TABLE name;
TRUNCATE TABLE EMP1;
11. Copying a table
CREATE TABLE EMP2 as
Select Empno,ename,sal,job,mgr,deptno from emp where deptno=20;

Oracle_sql (4) DDL tables and constraints