Create primary keys, foreign keys, annotations, constraints, indexes at the same time when Oracle builds tables

--PRIMARY key
CREATE TABLE EMP (ID number constraint ID_PR primary key, name1 varchar (8));
CREATE TABLE EMP9 (ID number, name1 varchar (8), constraint ABA_PR primary key (ID,NAME1));
--FOREIGN key
CREATE TABLE EMP1 (ID number references emp (ID), name varchar (8));

--composite FOREIGN key
CREATE TABLE Emp0 (ID number, name varchar (8), Constraint fk_nam1e foreign key (id,name) references EMP9 (id,name1));

--PRIMARY key addition
CREATE TABLE EMP2 (ID number,name varchar (8), id1 number, constraint pk_id primary key (ID), Constraint fk_name foreign key (ID 1) references emp (ID))
The wording of--check constraint
CREATE TABLE Emp4 (ID number check (ID in (3)), name varchar (8));

Without a constraint name:

CREATE TABLE UserInfo (

ID Number (6) primary key,--primary key

Name VARCHAR2 () NOT null,--non-null

Sex number (1),

Age Number (3) default 18,

Birthday date,

Address VARCHAR2 (50),

Email varchar2 Unique,--only

Tel Number (11),

Deptno Number (2) References Dept (DEPTNO)-FOREIGN key

);

With constraint name:

CREATE TABLE UserInfo (

ID number (6) Constraint ID_PK primary key,

Name VARCHAR2 (constraint) name_nn NOT NULL,

Sex number (1),

Age Number (3) default 18,

Birthday date,

Address VARCHAR2 (50),

Email VARCHAR2 (+) constraint Email_uqe unique,

Tel Number (11),

Deptno number (2) Constraint Dept_deptno_ref references dept (DEPTNO)

);

Column mode:

CREATE TABLE UserInfo (

ID Number (6),

Name VARCHAR2 (20),

Sex number (1),

Age Number (3) default 18,

Birthday date,

Address VARCHAR2 (50),

Email VARCHAR2 (25),

Tel Number (11),

Deptno Number (2),

Constraint ID_PK primary key (ID),--can also be more than two, federated primary key

Constraint Dept_deptno_ref foreign KEY (DEPTNO) References Dept (DEPTNO),

Constraint emial_name_uqe Unique (email, name)

);

Alter mode:

ALTER TABLE UserInfo Add (msn varchar2 (20));

ALTER TABLE UserInfo Modify (msn Varchar2 (25));

ALTER TABLE UserInfo drop (MSN);

ALTER TABLE userInfo drop constraint id_pk;

ALTER TABLE USERINFO ADD constraint ID_PK primary key (ID);

3. Create a View

CREATE TABLE V$_dept_view

As

Select Deptno, dname from dept;

--Recompile the view

Alter VIEW V$_dept_view compile;

Tip: A view is typically a query or subquery for a table or multiple tables, which reduces the amount of code, but also increases the level of maintenance of the database view, such as: A table field is deleted or modified, the view is recreated or modified, and a portion of the database is occupied; The view is a virtual table;

4. Create an index

Normal index: CREATE index idx_dpt_dname on dept (Dname);

Federated Index: CREATE index Idx_dept_dname_deptno on dept (Dname, Deptno);

--Unique index

Create unique index idx_emp_ename on scott.emp (ename);

--Reverse Key index

Create INDEX Idx_emp_rev_no on scott.emp (empno) reverse;

--Bitmap Index

Create bitmap index idx_emp_name on scott.emp (dname);

--Index organization table, must have primary key

CREATE Table Tab (

ID int PRIMARY KEY,

Name VARCHAR2 (20)

) Organization Index;

--The Insert efficiency of Index Organization table is very low

--Partition Table Index

CREATE index idx_name on table (col) Local/global;

--Index partition

Tip: When you create a primary KEY or UNIQUE key constraint for a table, the system also creates a constraint to that field, and also creates an index that takes up the database space, which improves efficiency when accessing and querying, but slows down when the table is modified;

5. Create a sequence

Create sequence seq;

Select Seq.nextval from dual;

Insert into tab values (Sql.nextval, ' music ');

Create sequence Seqtab

Start with and from 2

Increment by 3-each plus 3

nomaxvalue-No maximum value

MinValue 1-Minimum value 1

Nocycle-does not cycle

nocache;--does not cache

--Modify the sequence, cannot modify the starting value

Alter sequence Seqtab

MaxValue 1000;

6. Create synonyms

A synonym, as the name implies, is an alias or another name.

Create synonym scott_emp for scott.emp;

Create public synonym scott_dept for scott.dept;

SELECT * from Scott_emp;

SELECT * from Scott_dept;

7. CREATE TABLE Space

Create Tablespace Hooms

DataFile ' E:\HooMS.dbf '

Size 5M

Autoextend on next 2M maxsize 10M;

--Create user, assign can manipulate table space

Create user Hoo

Identified by Hoo

Default Tablespace hooms

Temporary tablespace temp;

--Create TABLE space

Create Tablespace myms

DataFile ' c:\myMS.dbf '

Size 1M

Autoextend on;

--scale table space--Modify table space size

ALTER DATABASE

DataFile ' c:\myMS.dbf '

Resize 2M;

--Extension tablespace--Add data file

Alter Tablespace myms

Add datafile ' c:\myMS_2.dbf '

Size 1M;

--Set the DBF file to grow automatically

ALTER DATABASE

DataFile ' c:\myMS_2.dbf '

Autoextend on next 2M maxsize 4M;

--Table Space renaming

Alter Tablespace myms

Rename to Hooms;

--Detach table space (offline)

Alter Tablespace Hooms

Offline temporary;

--Offline in archive mode

Alter Tablespace Hooms

Offline immediate;

--Bring the table space online

Alter tablespace hooms Online;

--Delete table spaces with no data

Drop Tablespace hooms;

--Delete table space with data

Drop Tablespace hooms

including contents;

Transferred from: http://blog.sina.com.cn/s/blog_8c10dd8f0101bete.html

Create primary keys, foreign keys, annotations, constraints, indexes at the same time when Oracle builds tables