Oracle table related DDL

Oracle Table: Data Definition Language (DDL) ) 1 Naming rules and conventions:The maximum length of the table name and column name is 30. The table name and column name must start with a letter. The table name or column name cannot contain blank or * reserved words on the Oracle server. It cannot be used as the table name or column name. 2 , Data type:When creating a data table, you must assign a data type to each column in the table. The data type specifies the type of data to be stored in this column. By providing a data type for a column, you can avoid storing incorrect data types in this column. Varchar2 :Varchar2
It is a balanced data type used to store data with variable-length letters and numbers in a column. Varchar2 must specify the maximum length. The minimum length and default length are both one character. Length
It should be placed in parentheses, such as varchar (20 ). If the data is shorter than the specified length, only the data value is stored, and no space is added at the end of the value. However, if the input value is longer than the specified length
Long, it will generate an error, and will not shorten the long value. If the value of a column does not have a fixed length, the varchar2 data type is the most suitable. Char :Char
A data type is a dense data type used to store data with fixed-length letters and numbers in a column. The default length and minimum length are both one character. If the input value is shorter than the specified length
Add a space to make the length equal to the specified length. If the input value is longer than the specified length, an error occurs. Char is applicable to fixed-length values. Number :Number
The data type is used to store negative numbers, positive numbers, integers, fixed points, and floating point numbers. The number data type can be used for any column for mathematical calculation. When a column uses the number type, you can specify its
Precision and scale. Precision is the total number of meaningful digits of a number, including digits on both sides of the decimal point, but the decimal point is not calculated when the precision is specified. Scale is the total number of digits on the right of the decimal point. Integer type: All digits, no decimal part. If a column is defined as an integer type, you only need to provide the precision. The number on the right of the decimal point of the specified decimal point type is variable. There can be any digit before the decimal point, or there can be no decimal point at all. When defining a column as this type, you do not need to specify the scale and precision along with the number type. Date :The date type is used to store Date and Time values. Columns of the date type store day, month, Century, hour, minute, and second. You do not need to specify the size for the date type. Long :The long type is used for variable-length character data. The maximum value is 2 GB bytes. Only one column in a data table can be defined as the long type. You do not need to specify its size when defining the long type. Nchar :The nchar type is similar to the char type, but it uses two bytes of binary encoding for each character, while the char type uses a single byte of acⅱ encoding for each character, it can only represent 256 different characters Clob :This large object data type is used to store single-byte character data, up to 4 GB bytes Nclob :This character uses the 2 byte character code for the big object data type Blob :The binary big object data type is used to store binary data. The maximum value is 4 GB bytes. 3 , ConstraintsA constraint is a rule applied to a data table. Constraints help us to make our database complete. Integrity rules: (1) Object Integrity: the columns in the primary key cannot be null. The primary key provides a unique method to identify one row or one entity. (2) integrity of reference: The foreign key value or null value, or the Oracle constraint must exist as the primary key value of the referenced table to implement the integrity rules of the relational database, data integrity is achieved at the individual column level. At any time, when a row or record in a data table is inserted, updated, or deleted, the operation can be successful only by meeting the constraints. 3.1 , Constraint type(1) integrity constraints: Define the primary key and foreign key in the data table, and the primary key referenced by the other key (2) value constraints: If the allowed value is null, if the required value is unique, and if a column value can only be a group of determined values, you need to define a value constraint. 3.2 , Naming ConstraintsOracle uses internal names or custom names to identify constraints. Each constraint name must be unique for each user account. A user cannot create constraints with the same name on two different data tables. Generally, naming constraints use the following conventions: <Table Name >_< column name >_< constraint type>Here, table name is the name of the table to define the constraint, column name is the name of the column to apply the constraint, and constraint type is the constraint type constraint expressed in the abbreviated form and the corresponding form of the abbreviation primary key: pkforeign key: fkunique: ukcheck: cknot NULL: NN if you do not name a constraint, the Oracle server automatically generates a constraint name in the format of sys_cn, N is a unique number. 3.3 Define ConstraintsYou can create constraints when creating a table or add constraints after the table is created. The constraint definition has two levels: column level: a column-level constraint only involves a unique column. Its definition and column definition are performed simultaneously. Except for key constraints and composite primary key constraints, other types of constraints can be defined at the column level. The common syntax format is: Column datatype [constraint constraint_name] constraint_typeTable level: a table-level constraint involves one or more columns. Its definition is separate from the column definition. Generally, table-level constraints are defined only after all column definitions are completed. Unless empty, all constraints can be defined at the table level. The common syntax format is: [Constraint constraint_name] constraint_type (Column name ) 3.3.1 , Primary key constraintsPrimary Key constraints are also known as entity integrity constraints. It creates a primary key for the table. A table can have only one primary key constraint. Whether a column is used as the primary key or a combination of multiple columns as the primary key, the primary key can only be non-null and have only unique values. For example, if the dept table of the N2 company database uses the deptid column as the primary key, the column-level definition constraints are as follows: deptid number (2) Constraint dept_deptid_pk primary key define constraints at the table level as follows: constraint dept_deptid_pk primary key (deptid) 3.3.2 Foreign key constraintsForeign key constraints are also known as primary key integrity constraints. It uses one or more columns as the foreign key and establishes the relationship between the foreign key and the primary key of the same table or another table to determine the foreign key of a data table, other referenced tables and their primary keys must already exist. Although the foreign key and the referenced primary key column do not need to have the same name, the foreign key value must match the primary key value of the parent table, alternatively, if the foreign key value is null, constraint must foreign key (facultyid) References Faculty (facultyid) Student's faultyid references the faculty table's primary key facultyid before the end of foreign key, you can also add
Cascade clause, which not only allows you to delete records in the parent table, but also deletes records that are dependent on the child table. Do not use on Delete
In case of a cascade clause, if the Sub-Table references records in the parent table, the row of records in the parent table cannot be deleted. 3.3.3 Non-empty ConstraintNon-null constraints ensure that a column has a value and is not a null value 3.3.4 And uniqueness constraintsThe Uniqueness constraint requires that each value in a column or group of columns is unique. If the uniqueness constraint is applied to a single column, this column has only unique values. If the uniqueness constraint is applied to a group of columns, this group of Columns has unique values. The Uniqueness constraint allows null values, unless this column also applies the not null non-null Constraint 3.3.5 Check ConstraintsCheck constraints define the conditions that each row must meet. A column can have multiple check constraints. Check constraints can be defined either at the column level or at the table level. You can also define constraint dept_deptid_cc check (deptid> = 10) and (deptid <= 99 )) 3.3.6 Non-empty check ConstraintsNon-null constraints can also be declared as check constraints. They can be defined at the column level or at the table level: Name varchar2 (15) Constraint faculty_name_ck check (name is not null) 3.3.7 And default Default ValueThe default value ensures that when a new row is inserted into the table, a specific column always has a value. If you enter another value, the default value will be overwritten. If a null value is inserted, the default value State char (2) default 'nj 'will be used' 4 Create Oracle Data TableThe general syntax format of the create table statement is: Create Table [schema.] tablename (Column1 datatype [constraint constraint_name] constraint_type , Column2 datatype [constraint constraint_name] constraint_type ,... [Constraint constraint_name] constraint_type (Column name ,... ),...) Schema is optional, it is the same as the user's registration name. tablename is the table name specified by the user. column is the name of a single column. ype is the data type and size of this column. constraint_name is the constraint name provided by the user. constraint_type indicates integrity constraints. or value constraints each column can have zero, one or more constraints defined at the column level, generally, table-level constraints are declared only after all column definitions are completed. 4.1 , CREATE TABLE Storage in the statement ClauseCreate
Table statements can include storage clauses, but they are optional. When creating a database, use this clause. The initial parameter is defined as the initial disk space allocated to the data table.
You can use another parameter "Next" to define how much disk space to append when the data table has used up the allocated initial disk space. Create Table Sample (ID number (3), name varchar (25) tablespace cis_datastorage (initial 1 m next 100 k) pctfree 20; // idle percentage 5 Display data table informationWhen you create one or more tables in the database, Oracle uses its own data dictionary to track all these tables. Oracle provides SQL statements and SQL * Plus commands to view information in the Oracle Data dictionary table. 5.1 View the user's table nameSelect table_name from user_tables; Oracle creates a system table to store information about users and user objects. user_tables is an Oracle system table and table_name is a column in this table. 5.2 View table structureDescribe table name 5.3 View ConstraintsOracle's data dictionary table user_constraints stores the constraints we input for each column. Select constraint_name, constraint_typefrom user_constraintswhere table_name = 'student '; indicates that uppercase letters must be used, because Oracle saves the table name in uppercase letters 5.4 View tablespace InformationA tablespace contains one or more physical data files. We can obtain information about all tablespaces that can be obtained using the data dictionary view user_tablespaces. The data dictionary view user_users provides users with information about their accounts and permanent temporary tablespace. 5.5 , Comment table and ColumnWhen creating a data table, you can add comments to the table and its columns to facilitate document writing. The comment statement used to add comments is a comment statement. For example: Comment on table student is 'table holds students for INDO-S text' comment on column employee. lname is 'last name'. You can use the data dictionary views all_tab_comments and all_col_comments to view all comments of tables and columns. 6 Modify an existing data tableTo modify an existing table, some modifications are allowed and some are not allowed. 6.1 Add new columns to an existing tableAlter table tablename add colname datatype; 6.2 Modify an existing columnAlter table tablename modify colname newdatatype; 6.3 Add a constraintAlter table tablename add [constraint constraint_name] constraint_type (column name ); 6.4 Delete a columnAlter table tablename drop column columnname; 6.5 Delete A ConstraintAlter table tablenameddrop constraint constraintname; 6.6 , Start/ Disable ConstraintsThe format for enabling or disabling constraints is: alter table tablenameenable | disable constraint constraintname; 6.7 , Rename a columnAlter table tablename rename column oldname to newname; 6.8 , Rename a constraintAlter table tablename rename constraint oldname to newname; 6.9 Modify the storage properties of a tableAlter table tablename storage (next NK) 7 Delete data tablesWhen a data table is deleted, all data and table structures in the table are permanently deleted; 8 , Rename the data tableRename oldtablename to newtablename;