ORACLE Database Table creation, auto-incrementing primary key, foreign key syntax explanation, oracle primary key

ORACLE Database Table creation, auto-incrementing primary key, foreign key syntax explanation, oracle primary key
1. Create a table structure:

Drop table base_list;

Create table base_list (

"DATA_LIST_ID" number not null,

"BASE_DATA_ID" number not null,

"BASE_DATA_LIST" VARCHAR2 (100 BYTE) not null,

"FLAG" number default 0 NOT NULL

)

LOGGING

NOCOMPRESS

NOCACHE

Sequence 2:

Create sequence SEQ_DATA_LIST_ID-SEQ_DATA_LIST_ID auto-increment Column

Increment by 1-add a few at a time

Start with 1-count from 1

NOMAXVALUE-do not set the maximum value

NOCYCLE-always accumulate, no loop

NOCACHE

Trigger creation:

Create or replace trigger "BSQAM1". "T_DATA_LIST_ID" before insert on "BSQAM1". "BASE_LIST" referencing old as "OLD" new as "NEW" FOR EACH ROW

Begin

-Assign the value of the auto-increment primary key field to nextval of sequence before addition.

Select SEQ_DATA_LIST_ID.nextval INTO: NEW. DATA_LIST_ID from dual;

End;

4. Create a foreign key:

-Use alter table to create a foreign key

Alter table CERTIFICATE

Add constraint F_USER_ID

Foreign key (USER_ID)

References TUSER (USER_ID );