如何让Oracle表字段自动增长的学习例子:
1,订单表order1:
order_id | 订单号 | Number(30) | Not null | Pk |
user_id | 用户编号 | Number(30) | Not null | Fk |
pro_id | 产品号 | Number(30) | Not null | Fk |
p_quantity | 消费产品数量 | Number(10) | Not null |
|
pay | 消费金额 | Number(10,2) |
|
|
c_number | 信用卡号 | Number(30) |
|
|
pay_id | 支付方式编号 | Number(5) | Not null | Fk |
state-id | 订单状态编号 | Number(5) | Not null | Fk |
|
|
|
|
|
2, --创建订单表
create table order1(
order_id number(30) constraint pk_order_id primary key,
user_id number(30) not null,
pro_id number(30) not null,
p_amount number(10),
pay number(10),
c_number number(30),
pay_id number(5) not null,
state_id number(5) not null
);
--建立外键
alter table order1
add constraint order1_fk1 foreign key (pay_id)references pay(pay_id);
alter table order1
add constraint order1_fk2 foreign key (state_id) references state(state_id);
alter table order1
add constraint order1_fk3 foreign key (pro_id) references product(pro_id);
alter table order1
add constraint order1_fk4 foreign key (user_id) references use(user_id);
3,--自动生成订单序列
create sequence t_order_id_seq
increment by 1
start with 8100001
nomaxvalue
nocycle;
4,--创建触发器
create or replace trigger order_id_trigger
before insert
on order1
for each row
declare
newid number(18,0);
begin
select t_order_id_seq.nextval into newid from dual;
:new.order_id:=newid;
end;