Oracle建表，建序列，建触发器，自动生成唯一主键

--建表t_account

create table t_account(
 accountID number(8) not null,
 accountName varchar(100) not null
)tablespace portalone;
alter table t_account add constraint pk_account primary key(accountID) using index tablespace portaloneindx;

 

--建序列
create sequence s_account
minvalue 1
maxvalue 99999999
start with 1
increment by 1
cache 20;

 

--建触发器
create or replace trigger trigger_t_account
before insert on t_account
for each row when(new.accountID is null)
declare
begin
   --从序列中获取新的序号并赋给新行的ID字段
   select s_account.nextval into :new.accountID from dual;
   --获取系统的guid/uuid生成主键
   --select sys.guid() into :New.accountID from dual;
   --获取系统时间作为表的某个字段值
   --select to_char(sysdate, "YYYY-MM")into :New.YEAR_MONTH from dual;
end trigger_t_account;   

 

--建表t_order
drop table t_order;
create table t_order(
 orderID number(8) not null,
 accountID number(8) not null,
 orderName varchar(100) not null
)tablespace portalone;
alter table t_order add constraint pk_order primary key(orderID) using index tablespace portaloneindx;
alter table t_order add constraint fk_order foreign key(accountID) references t_account(accountID);

 

--建立序列
create sequence s_order
minvalue 1
maxvalue 99999999
start with 1
increment by 1
cache 20;

 

--建触发器
create or replace trigger trigger_t_order
before insert on t_order
for each row when (new.orderID is null)
declare
begin
    select s_order.nextval into :new.orderID from dual;
    select s_order.currval into :new.accountID from dual;
end trigger_t_order;

 

--建表t_orderItem
drop table t_orderItem;
create table t_orderItem(
 orderItemID number(8) not null,
 orderID number(8) not null,
 orderItemName varchar(100) not null
)tablespace portalone;
alter table t_orderItem add constraint pk_orderItem primary key(orderItemID) using index tablespace portaloneindx;
alter table t_orderItem add constraint fk_orderItem foreign key(orderID) references t_order(orderID);

 

--建序列
create sequence s_orderItem
minvalue 1
maxvalue 99999999
start with 1
increment by 1
cache 20;

 

--建触发器
create or replace trigger trigger_t_orderItem
before insert on t_orderItem
for each row when (new.orderItemID is null)
declare
begin
    select s_orderItem.nextval into :new.orderItemID from dual;
    select s_orderItem.currval into :new.orderID from dual;
end trigger_t_orderItem;