oracle how to create table

Oracle is one of the most popular relational databases at present. Its table creation is relatively complicated, but as long as you understand the rules and follow the correct methods, you can easily create a table that meets the requirements. Let's introduce in detail how Oracle creates tables.

1. Preparation

Before creating a table in Oracle, you first need to determine the table name and its field names, which will affect future database operations. (Note: Oracle is case-insensitive, please choose the spelling format according to the actual situation)

2. Create table structure

Creating table structure refers to defining the fields of the table, Type, length and constraints. In Oracle, you can use the following statement to create a simple table:

CREATE TABLE 表名(
  字段1 数据类型(长度) [约束条件],
  字段2 数据类型(长度) [约束条件],
  ...
  字段n 数据类型(长度) [约束条件]
);

Or use the following statement:

CREATE TABLE 表名(
  字段1 数据类型,
  字段2 数据类型,
  ...
  字段n 数据类型,
  CONSTRAINT 约束名1 约束条件(列名),
  CONSTRAINT 约束名2 约束条件(列名)
);

Among them, the data type and length are required fields, and the following are commonly used data types:

• NUMBER: numeric type, including integer and floating point type;
• VARCHAR2: variable length string;
• CHAR: fixed length string;
• DATE: Date type;
• CLOB: Large text type;
• BLOB: Binary large object type.

Constraints refer to restrictions on data. The following are commonly used constraints:

• NOT NULL: not empty;
• PRIMARY KEY: primary key ;
• UNIQUE: uniqueness;
• FOREIGN KEY: foreign key;
• CHECK: check condition.

For example, create a table named students, containing three fields: id, name and gender, where id is the primary key, name is a string type with a length of 20, and gender is a string type. The length is 1 and cannot be empty. You can use the following statement to create it:

CREATE TABLE students(
  id NUMBER(10) PRIMARY KEY,
  name VARCHAR2(20) NOT NULL,
  gender CHAR(1) NOT NULL
);

3. Add constraints

In the process of creating a table, you can add multiple constraints , used to limit the correctness of data. The following are some examples of constraints:

• PRIMARY KEY: Primary key constraints are used to identify unique records in the table. Each table can only have one primary key, and the primary key cannot be empty.

CONSTRAINT pk_students PRIMARY KEY(id)

• UNIQUE: The unique key constraint is used to identify each record in the table as having a unique value. Each table can have multiple unique keys.

CONSTRAINT uk_students UNIQUE(name)

• FOREIGN KEY: Foreign key constraints are used to establish connections between tables. Foreign key columns must reference primary or unique keys in other tables.

CONSTRAINT fk_students FOREIGN KEY(dept_id) REFERENCES departments(dept_id)

• CHECK: Check constraints are used to limit the values ​​inserted or updated into the table. According to the given conditions, only rows that meet the requirements can be inserted or updated, otherwise they will be rejected.

CONSTRAINT ck_students_gender CHECK(gender IN ('M', 'F'))

4. Creating a table

After defining the structure and constraints of the table, you can start creating the table. In the SQLPLUS environment, you can enter the following statement to create a table:

SQL> CREATE TABLE students(
  2    id NUMBER(10) PRIMARY KEY,
  3    name VARCHAR2(20) NOT NULL,
  4    gender CHAR(1) NOT NULL
  5  );

or use the following statement:

SQL> CREATE TABLE students(
  2    id NUMBER(10),
  3    name VARCHAR2(20),
  4    gender CHAR(1),
  5    CONSTRAINT pk_students PRIMARY KEY(id),
  6    CONSTRAINT uk_students UNIQUE(name),
  7    CONSTRAINT ck_students_gender CHECK(gender IN ('M', 'F'))
  8  );

At this time, Oracle will return a success message, indicating that the table has been created successfully.

Summary: The above is the process of creating a table in Oracle. The steps are simple, but the table structure and constraints need to be determined according to the actual situation, which will help with future database operations and maintenance.

The above is the detailed content of oracle how to create table. For more information, please follow other related articles on the PHP Chinese website!