Create table Depot
(dno varchar2(5),
dname varchar2(20),
daddress varchar2(20),
constraint pk_dno primary key(dno) );
Create table BusType
(tno varchar2(5),
tdescript varchar2(20),
constraint pk_tno primary key(tno) );
Create table BusDriver
(bdno varchar2(5),
bdname varchar2(20),
bdsalary number(6,2),
pcvdate date,
dno varchar2(5),
constraint pk_bdno primary key(bdno),
constraint fk_dno foreign key(dno) references Depot(dno) );
Create table Cleaner
(cno varchar2(5),
cname varchar2(20),
csalary number(6,2),
dno varchar2(5),
constraint pk_cno primary key(cno),
constraint fk_dno1 foreign key(dno) references Depot(dno) );
Create table Route
(rno varchar2(5),
rdescript varchar2(30),
dno varchar2(5),
constraint pk_rno primary key(rno),
constraint fk_dno2 foreign key(dno) references Depot(dno) );
Create table Bus
(reg_no varchar2(10),
model varchar2(20),
tno varchar2(5),
dno varchar2(5),
cno varchar2(5),
constraint pk_reg_no primary key(reg_no),
constraint fk_tno foreign key(tno) references BusType(tno),
constraint fk_dno3 foreign key(dno) references Depot(dno),
constraint fk_cno foreign key(cno) references Cleaner(cno));
Create table Ability
(bdno varchar2(5),
rno varchar2(5),
constraint pk_drroute primary key(bdno, rno),
constraint fk_bdno foreign key(bdno) references busdriver(bdno),
constraint fk_rno foreign key(rno) references route(rno) );Create table Training
(bdno varchar2(5),
tno varchar2(5),
trainingdate date,
constraint pk_drbustype primary key(bdno, tno),
constraint fk_bdno2 foreign key(bdno) references busdriver(bdno),
constraint fk_tno2 foreign key(tno) references BusType(tno));Create table Restriction
(rno varchar2(5),
tno varchar2(5),
constraint pk_rbustype primary key(rno, tno),
constraint fk_rno2 foreign key(rno) references route(rno),
constraint fk_tno3 foreign key(tno) references BusType(tno));
====================================================================================
insert into Depot values
('101','Holloway','Camden Road');insert into Depot values
('102','Hornsey','High Road');insert into Depot values
('104','Islington','Upper Street');
--load BusType with data insert into BusType values
('1','doubledecker');insert into BusType values
('2','metrobus');insert into BusType values
('3','midibus');insert into BusType values
('4','bendy bus');insert into BusType values
('5','open top');
--load Busdriver with data insert into Busdriver values
('001','Jane Brown',1800,'09-feb-1985','101');insert into Busdriver values
('006','Sally Smith',1750,'09-mar-1996','');insert into Busdriver values
('007','James Bond',1500,'09-jan-1999','102');insert into Busdriver values
('008','Maggie May',2200,'09-jan-2000','102');insert into Busdriver values
('009','Jack Jones',1400,'09-aug-2001','101');insert into Busdriver values
('010','Peter Piper',3500,'09-jun-2004','104');insert into Busdriver values
('011','John Peel',2000,'09-feb-2005','102');--load Cleaner with data insert into Cleaner values
('110','John',2550,'101');insert into Cleaner values
('111','Jean',2500,'101');insert into Cleaner values
('112','Betty',2400,'102');insert into Cleaner values
('113','Vince',2800,'102');insert into Cleaner values
('114','Jay',3000,'102');insert into Cleaner values
('115','Doug',2000,'102');insert into Cleaner values
('116','Geeta',4000,'');
--load Route with data insert into Route values
('10','Tottenham/Angel','102');insert into Route values
('11','Islington/Highgate','102');insert into Route values
('6','Camden/Golders Green','101');insert into Route values
('7','Finchley/Tottenham','101');insert into Route values
('8','Hendon/Muswell Hill','101');
--load Bus with data insert into Bus values
('A123ABC','Routemaster','1','101','113');insert into Bus values
('D678FGH','Volvo 8700','2','101','114');insert into Bus values
('D345GGG','Volvo 8500','1','101','112');insert into Bus values
('H259IJK','Daf SB220','3','102','114');insert into Bus values
('P200IJK','Mercedes 709D','2','102','110');insert into Bus values
('P300RTY','Mercedes Citaro','4','102','111');insert into Bus values
('R678FDS','Daf SB220','1','','110');--load Ability with data insert into Ability values
('001','6');insert into Ability values
('001','7');insert into Ability values
('001','8');insert into Ability values
('007','6');insert into Ability values
('007','10');insert into Ability values
('008','10');insert into Ability values
('008','11');insert into Ability values
('009','7');
--load Training with data insert into Training values
('001','1','09-jan-2006');insert into Training values
('001','2','09-jan-2006');insert into Training values
('006','2','09-feb-2006');insert into Training values
('007','1','09-feb-2006');insert into Training values
('007','2','09-feb-2006');insert into Training values
('007','3','09-mar-2006');insert into Training values
('008','2','09-mar-2006');insert into Training values
('008','3','09-mar-2006');insert into Training values
('008','4','09-apr-2006');insert into Training values
('009','3','09-apr-2006');insert into Training values
('009','4','09-may-2006');insert into Training values
('011','1','09-may-2006');insert into Training values
('011','2','09-may-2006');insert into Training values
('011','3','09-jun-2006');insert into Training values
('011','4','09-jun-2006');insert into Training values
('011','5','09-jun-2006');
--load Restriction with data insert into Restriction values
('6','1');insert into Restriction values
('6','2');insert into Restriction values
('6','3');insert into Restriction values
('6','4');insert into Restriction values
('7','1');insert into Restriction values
('7','2');insert into Restriction values
('8','3');insert into Restriction values
('8','4');insert into Restriction values
('10','1');insert into Restriction values
('10','2');insert into Restriction values
('10','3');insert into Restriction values
('10','4');insert into Restriction values
('11','1');insert into Restriction values
('11','2');insert into Restriction values
('11','3');insert into Restriction values
('11','4');
commit;select * from Ability;
select * from Training;
select * from Restriction;
select * from Busdriver;
select * from Route;
select * from Bus;
select * from Cleaner;
select * from BusType;
select * from Depot;
解决方案 »
- 求推荐书籍
- 将oracle表中的数据每10分钟更新一次,怎么实现
- Oracle触发器
- 数据库的分部式部署
- SQL 批量插入性能问题,期待高手!
- 启动失败,ORA-01122: 数据库文件 2 验证失败_急,请高手
- sql语言的向oracle转化 在线等!!!
- 请问oracle8i里创建pl/sql的存储过程在什么环境下输入
- 在Win2000下,为什么每次重启动后,这个OracleOraHome8iManagementServer服务都不能自动启动呢?明明设置了自动启动,可是每次都要手动启
- 谁知道传说中<<ORACLE初学者指南>>的下载地址,请tell me.谢谢!
- oracle9i作业问题
- 我在存储过程里面写 create table t1;怎么在plsql命令行里面执行通不过呢?
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_DNO)insert into Depot values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_DNO)insert into Depot values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_DNO)insert into BusType values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_TNO)insert into BusType values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_TNO)insert into BusType values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_TNO)insert into BusType values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_TNO)insert into BusType values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_TNO)('001','Jane Brown',1800,'1985/02/09','101')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('006','Sally Smith',1750,'1996/03/09','')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('007','James Bond',1500,'1999/01/09','102')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('008','Maggie May',2200,'2000/01/09','102')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('009','Jack Jones',1400,'2001/08/09','101')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('010','Peter Piper',3500,'2004/06/09','104')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('011','John Peel',2000,'2005/02/09','102')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配insert into Cleaner values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_CNO)insert into Cleaner values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_CNO)insert into Cleaner values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_CNO)insert into Cleaner values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_CNO)insert into Cleaner values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_CNO)insert into Cleaner values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_CNO)insert into Cleaner values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_CNO)insert into Route values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RNO)insert into Route values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RNO)insert into Route values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RNO)insert into Route values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RNO)insert into Route values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RNO)insert into Bus values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_REG_NO)insert into Bus values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_REG_NO)insert into Bus values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_REG_NO)insert into Bus values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_REG_NO)insert into Bus values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_REG_NO)insert into Bus values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_REG_NO)insert into Bus values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_REG_NO)insert into Ability values
*第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SYSTEM.FK_BDNO) - 未找到父项关键字insert into Ability values
*第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SYSTEM.FK_BDNO) - 未找到父项关键字insert into Ability values
*第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SYSTEM.FK_BDNO) - 未找到父项关键字insert into Ability values
*第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SYSTEM.FK_BDNO) - 未找到父项关键字insert into Ability values
*第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SYSTEM.FK_BDNO) - 未找到父项关键字insert into Ability values
*第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SYSTEM.FK_BDNO) - 未找到父项关键字insert into Ability values
*第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SYSTEM.FK_BDNO) - 未找到父项关键字insert into Ability values
*第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SYSTEM.FK_BDNO) - 未找到父项关键字('001','1','2006/01/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('001','2','2006/01/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('006','2','2006/02/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('007','1','2006/02/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('007','2','2006/02/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('007','3','2006/03/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('008','2','2006/03/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('008','3','2006/03/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('008','4','2006/03/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('009','3','2006/04/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('009','4','2006/05/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('011','1','2006/05/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('011','2','2006/05/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('011','3','2006/06/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('011','4','2006/06/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配('011','5','2006/06/09')
*第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)insert into Restriction values
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)
提交完成。未选定行
未选定行
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYSTEM.PK_RBUSTYPE)
比如你插入的行:(我是复制你的)
insert into Training values
('006','2','09-feb-2006'); insert into Training values
('007','1','09-feb-2006'); insert into Training values
('007','2','09-feb-2006'); insert into Training values
('007','3','09-mar-2006'); insert into Training values
('008','2','09-mar-2006'); insert into Training values
('008','3','09-mar-2006'); insert into Training values
('008','4','09-apr-2006');
像007,008只能出现一次!还有这样的错误 第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SYSTEM.FK_BDNO) - 未找到父项关键字 是因为你定义了外键,这样数据库就有了完整性约束,就是必须在父表里面相应的数据。
第 2 行出现错误:
ORA-01861: 文字与格式字符串不匹配 这个错误是定义的数据类型和你输入的不匹配
不过我我有一个疑问你插入第一个表Depot中应该是正确的,我在我的机子上试过了,我的提示:SQL> Create table Depot
2 (dno varchar2(5),
3 dname varchar2(20),
4 daddress varchar2(20),
5 constraint pk_dno primary key(dno) ); 表已创建。SQL> insert into Depot values
2 ('101','Holloway','Camden Road'); 已创建 1 行。SQL>
SQL> insert into Depot values
2 ('102','Hornsey','High Road'); 已创建 1 行。SQL>
SQL> insert into Depot values
2 ('104','Islington','Upper Street'); 已创建 1 行。如果所有的都插不进去,你要考虑你的oracle是不是出问题了?