Oracle创建表和主键自增
Oracle创建表和主键自增
创建表:
create table tuser(
id number(11) not null,
name varchar2(20) not null,
password varchar2(20),
birthday date,
constraint tuser_pk primary key (id)
);
创建序列:
create sequence increase_seq increment by 1 start with 1 nomaxvalue nocycle cache 10;
创建trigger:
create or replace trigger tuser_trigger
before insert on tuser for each row
begin
select increase_seq.nextval into :new.id from dual;
end;
/
根据使用的工具,可能需要增加“/”来执行PL/SQL块。
测试:
insert into tuser(name,password,birthday) values('wujay','123456',null);
commit;
select * from tuser;
ID NAME PASSWORD BIRTHDAY
---------- -------------------- -------------------- --------------
1 wujay 123456
修改表:
alter table tuser rename column id to pk_tuser;