--創建臨時表空間
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中只有創建序列和觸發器才可以解決主鍵自增長的問題