Creation of primary key, foreign key, index, sequence, uniqueness constraint in Oracle

1, the creation of the primary key

Method One: Declare the field PRIMARY KEY constraint directly in the SQL statement

CREATE TABLE table_name (ID type[length] constraint pk_name primary key,name tyoe[length],age type[length],class_id);

Method two: Alter change table ADD constraint

ALTER TABLE TABLE_NAME ADD constraint Pk_name primary key (field);

Delete:

ALTER TABLE table_name DROP constraint contraint_name;

Rename:

ALTER TABLE TABLE_NAME rename constraint old_name to new_name;

Failure:

ALTER TABLE TABLE_NAME MODIFY constraint constraint_name disable;

Effect:

ALTER TABLE TABLE_NAME MODIFY constraint constraint_name enable;

2. Creation of foreign keys

Method One: Declare directly in the SQL statement

CREATE TABLE table_name (ID type[length] PRIMARY key table_name_pk,name Tyoe[length],age Type[length],

Class_id,foreign Key (class_id) references Table2_name (class_id));//field type to be consistent

Method two: Alter change table ADD constraint

ALTER TABLE TABLE_NAME ADD constraint fk_name foreign key (class_id) references Table2_name (class_id);

Delete:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Rename:

ALTER TABLE TABLE_NAME rename constraint old_name to new_name;

Failure:

ALTER TABLE TABLE_NAME MODIFY constraint constraint_name disable;

Effect:

ALTER TABLE TABLE_NAME MODIFY constraint constraint_name enable;

3, the creation of the index

CREATE INDEX index_name on table_name (field);

Delete: Drop index index_name;

Rename: Alter index index_name rename to new_name;

Effect:

Failure:

4. Creation of sequences

Create sequence Sequence_name

Start with 1

Increment by 1

Nomaxvalue

Nocycle;

Delete: Drop sequence sequence_name;

5. Creation of uniqueness constraints

CREATE TABLE table_name (ID type[length],name type[length],

Num type[length] Constraint constraint_name unique,class_id type[length]);

ALTER TABLE TABLE_NAME ADD constraint constraint_name unique (one or more fields);

Creation of primary key, foreign key, index, sequence, uniqueness constraint in Oracle