Oracle入门精读85-Oracle建表完整案例

创建部门表

SQL> create table hr_dept(

id number(7),

deptName varchar2(20) not null,

constraint pk_dept_id primary key(id),

constraint un_dept_deptName unique(deptName)

)

创建人员表

SQL> create table hr_emp(

id number(5),

last_name varchar2(10) not null,

first_name varchar2(10) not null,

userid varchar2(8),

start_date date default sysdate,

title varchar(10),

dept_id number(7),

salary number(11,2),

user_type varchar2(4),

constraint pk_emp_id primary key(id),

constraint un_emp_first_name unique(first_name),

constraint un_emp_userid unique(userid),

constraint un_emp_title unique(title),

constraint fk_emp_deptid  foreign key(dept_id) references hr_dept(id)

)

INITRANS 1 MAXTRANS 255 

PCTFREE  20  PCTUSED  50

STORAGE(

INITIAL  1024K 

NEXT  1024K 

PCTINCREASE  0 

MINEXTENTS  1 

MAXEXTENTS  5

)

TABLESPACE sample1;

显示上述表参数

SQL> select * from user_tables where table_name like 'HR%'

SQL> SELECT constraint_name,constraint_type,table_name,search_condition,index_name FROM USER_CONSTRAINTS where table_name like 'HR%';