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 );