How to set the oracle primary key

How to set the oracle primary key

Primary key:

1. primary key constraint: A table can have only one primary key constraint. The primary key can be a single field or multiple fields. In either case, all the fields are not null.
2. Unique constraint: A table can have multiple Unique constraints. The Unique field can be NULL.
3. Primary Key and Unique: The difference is that a table can have only one primary key constraint, but multiple Unique constraints. All primary key fields are not null, and unique can be

Null; the same point is that the uniqueness can be ensured.
4. Primary Key, Unique, and index: the primary key constraint and Unique constraint will become indexes by default. When the primary key and Unique have multiple fields, there is an index prefix problem, that is, the where language

The condition in the sentence must have the first field of the primary key or unique, otherwise the index will not be used.
5. Foreign key and primary key, Unique: The foreign key must be the primary key or Unique index of another table (parent table. If you want to add a record while the parent table does not, an error is returned. Otherwise, such

If you want to delete the records in the parent table and the child table has records, an error is returned. However, if you use on delete cascade when creating a foreign key constraint

, Directly Delete the data associated with the sub-table without an error.
If you want to delete the parent table, add cascade constraints. In this case, the foreign key of the child table is removed, and the records in the table remain unchanged.
Foreign key:

The foreign key is the joined field between the table and another table.
The foreign key must be the primary key of another table.
Foreign keys are used to ensure data integrity. It generally includes the following types:
Entity integrity ensures that each entity is unique (implemented through primary keys ).

There are two ways to set the oracle primary key:
One is to set an auto-increment primary key, and the other is to generate a unique sequence;
1. Auto-increment primary key

SQL> create table Prim_key_Increase_Test (
2 id number (10) primary key,
3 name varchar2 (30)
4 );

The table has been created.

SQL> create sequence Prim_key_Incre_Sequence
2 minvalue 1
3 nomaxvalue
4 start with 1
5 increase by 1
6 nocycle
7 nocache;
Increase by 1
*
Row 3 has an error:
ORA-00933: SQL command ended incorrectly

SQL> 5
5 * increase by 1
SQL> c/increase/increment/
5 * increment by 1
SQL> r
1 create sequence Prim_key_Incre_Sequence
2 minvalue 1
3 nomaxvalue
4 start with 1
5 increment by 1
6 nocycle
7 * nocache

The sequence has been created.

SQL> create trigger Prim_key_trigger before
2 insert on Prim_key_Increase_Test for each row
3 begin
4 select Prim_key_Incre_Sequence.nextval into: New. id from dual;
5 end;
6/

Trigger created

SQL> commit;

Submitted.

SQL> insert into Prim_key_Increase_Test (id) values ('20140901 ');

One row has been created.

SQL> select * from Prim_key_Increase_Test;

ID NAME
----------------------------------------
1

SQL> insert into Prim_key_Increase_Test (id) values ('20140901 ');

One row has been created.

SQL> insert into Prim_key_Increase_Test (id) values ('20140901 ');

One row has been created.

SQL> select * from Prim_key_Increase_Test;

ID NAME
----------------------------------------
1
2
3