Oracle 建表，添加主外键，序列，触发器

select * from user_objects where object_type='TABLE';
--删除contact表,包括删除与其相关的约束
drop table contact cascade constraints
--创建contact表
create table contact(
id number(6) not null primary key 
,first_name varchar2(20) not null
,last_name varchar2(20) not null
,birth_date Date
--,constraint pk_contact primary key(id) --指定主主键
);

drop sequence contact_seq;
--创建表contact的自增序列
create sequence contact_seq
 start with 1
 increment by 1
 nomaxvalue
 nocycle
 nocache

drop trigger contact_trigger
--创建表contact自增序列的触发器
create or replace trigger contact_trigger 
before insert on contact 
for each row  
begin
    select contact_seq.nextval into:new.id from sys.dual;
end;

insert into contact(first_name,last_name,birth_date) values('Clarence','Ho',to_date('1980-07-30','yyyy-mm-dd'));
insert into contact(first_name,last_name,birth_date) values('Scott','Tiger',to_date('1990-11-02','yyyy-mm-dd'));
insert into contact(first_name,last_name,birth_date) values('John','Smith',to_date('1964-02-28','yyyy-mm-dd'));

--删除contact_tel表
drop table contact_tel cascade constraints;
--创建contact_tel表
create table contact_tel(
id number(6) not null primary key
,contact_id number(6) not null
--,contact_id number not null references contact(id)
,tel_type varchar2(20) not null
,tel_number varchar2(20) not null
--,constraint pk_contact_tel primary key(id) --主键
,constraint fk_contact_tel foreign key(contact_id) references contact(id) --外键
);
drop sequence contact_tel_seq;
--创建表contact_tel的自增序列
create sequence contact_tel_seq
 increment by 1
 start with 1
 nomaxvalue
 nocycle
 nocache     
drop trigger contact_tel_trigger
--创建表contact_tel自增序列的触发器 
create or replace trigger contact_tel_trigger
before insert on contact_tel 
for each row
begin
    select contact_tel_seq.nextval into:new.id from dual;
end;

insert into contact_tel(contact_id,tel_type,tel_number) values (1,'Mobile','1234567890');
insert into contact_tel(contact_id,tel_type,tel_number) values (1,'Home','2234567890');
insert into contact_tel(contact_id,tel_type,tel_number) values (2,'Home','3234567890');

--以下测试主从表的外键约束是否生效
insert into contact_tel(contact_id,tel_type,tel_number) values (4,'Home','4234567890');

select * from contact;
select * from contact_tel;