Home >Database >Oracle >How to create a table in Oracle?

How to create a table in Oracle?

coldplay.xixi
coldplay.xixiOriginal
2020-07-16 16:35:038659browse

How to create a table in oracle: 1. It must start with a letter; 2. Do not use reserved words in SQL. When you must use it, you can enclose the string in double quotes; 3. Use English words related to entities or attributes. There are certain restrictions on symbol length.

How to create a table in Oracle?

How to create a table in Oracle:

1. Syntax for creating a table:

create table 表名(       
字段名1  字段类型(长度)   是否为空,        
字段名2  字段类型           是否为空 );

-Add a primary key

alter table 表名 add constraint 主键名 primary key (字段名1);

-Add a foreign key:

alter table 表名 add constraint 外键名 foreign key (字段名1) references 关联表 (字段名2);

2. Add default values ​​and constraints to the fields when creating the table

When creating a table, you can add a default value to the field. For example: Date field DEFAULT SYSDATE. In this way, every time you insert or modify the field, you can get the action time without using the program to operate the field.

For example:

IS_SEND    NUMBER(1) default 1        --是否已发

When creating a table, you can add constraints to the fields, for example: NOT NULL, repeated UNIQUE is not allowed, keyword PRIMARY KEY, conditional check CHECK (condition), foreign key REFERENCES table name (field name)

3. Example of creating a table

create table DEPT(
  DNAME      varchar2(14),                  
  LOC           varchar2(6),   
  EPTNO       number(2)   constraint PK_DEPT  primary  KEY,                     
);
create table region(
  ID                    number(2) not null primary KEY,                  
  postcode           number(6) default '0' not null,   
  areaname          varchar2(30) default '' not null,                     
);

4. Naming rules and precautions 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 is not distinguished

3 ) Do not use reserved words in SQL. When you must use it, you can enclose the string in double quotes

4) There are certain restrictions on the length of English symbols related to entities or attributes

5) Constraints The naming rules and syntax of the name, if not specified when creating the table, the system naming rules are SYS_Cn (n is a number). The naming rules of the constraint name string are the same as the naming rules of table and field names

6) Precautions when using constraints. System functions cannot be used in constraints. For example, when comparing SYSDATE with fields in other tables, you can use comparison of fields in this table.

Notes:

1) You can use Chinese field names when creating a table, but it is better to use English field names

2) When creating a table, you should put smaller non-empty fields in At the front, fields that may be empty are placed at the back

3) If there are unique keywords or unique constraints when creating a table, an index will be automatically created when creating the table

4) A table The maximum number of fields is also limited, 254.

Related learning recommendations: oracle database learning tutorial

The above is the detailed content of How to create a table in Oracle?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn