--创建临时表空间
create temporary tablespace tour_temp tempfile 'd:\OracleData\tour_temp.dbf' size 10m autoextend on next 10m maxsize unlimited extent
management local;
--创建数据表空间
create tablespace tour_data logging datafile 'd:\OracleData\tour_data.dbf' size 20m autoextend on next 20m maxsize unlimited extent
management local;
--创建用户并指定表空间
create user tour identified by tour default tablespace tour_data temporary tablespace tour_temp;
--给用户授予权限
grant connect,resource,dba to tour;
--连接用户或用户登录
conn tour/tour;
创建表
非主键自增长
主表
create table EMB_T_Role
(
emb_c_roleID int not null,
emb_c_roleEnName varchar2(255) not null,
emb_c_roleZhName varchar2(255) not null,
emb_c_displayOrder int not null,
primary key (emb_c_roleID)
)tablespace tour_data;
字表
create table EMB_T_RoleMenu
(
emb_c_roleID int not null,
emb_c_menuID int not null
)tablespace tour_data;
外键
alter table EMB_T_RoleMenu add constraint FK_role_rmenu_roleID foreign key (emb_c_roleID)
references EMB_T_Role (emb_c_roleID);
主键自增长
创建表
CREATE TABLE EG_THEME
(
THEMEID INTEGER,
THEMENAME varchar2(256),
MEMO varchar2(1000),
constraint PK_THEME_ID primary key (THEMEID)
) tablespace dataInfo_data;
创建序列
create sequence seq_THEME_THEMEID increment by 1 start with 10001 maxvalue 999999999 minvalue 1;
创建出发器
create or replace trigger tri_THEME_THEMEID
before insert on EG_THEME for each row
begin
select seq_THEME_THEMEID.nextval into:new.THEMEID from dual;
end;
/
在oracle中只有创建序列和触发器才可以解决主键自增长的问题