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;
Oracle 建表,添加主外键,序列,触发器
最新推荐文章于 2023-06-08 14:03:11 发布