创建2个主子表添加外键时 给主键中的某列加外键 开始时候有错误 说是 : 不是唯一或主键
百度了一下需要加 unique 但是加了的话 那字表中不是只能有一个这样的主键了么 该怎么办
代码如下
create table t_item
(
item_id varchar2(32) not null primary key,
item_name varchar2(32),
item_cal_Id varchar2(32) unique,
item_kal_Id varchar2(32) unique
);create table t_item_cal
(
item_cal_Id varchar2(32) not null primary key,
item_cal_name varchar2(32),
constraint fk_item_cal foreign key(item_cal_Id) references t_item(item_cal_Id)
);create table t_item_kal
(
item_kal_Id varchar2(32) not null primary key,
item_kal_name varchar2(32) ,
constraint fk_item_kal foreign key(item_kal_Id) references t_item(item_kal_Id)
);insert into t_item(item_id,item_name,item_cal_Id,item_kal_Id) values (sys_guid(),'name1',sys_guid(),sys_guid());
insert into t_item(item_id,item_name,item_cal_Id,item_kal_Id) values (sys_guid(),'name2',sys_guid(),sys_guid());
insert into t_item(item_id,item_name,item_cal_Id,item_kal_Id) values (sys_guid(),'name3',sys_guid(),sys_guid());
insert into t_item(item_id,item_name,item_cal_Id,item_kal_Id) values (sys_guid(),'name4',sys_guid(),sys_guid());
insert into t_item(item_id,item_name,item_cal_Id,item_kal_Id) values (sys_guid(),'name5',sys_guid(),sys_guid());insert into t_item_cal(item_cal_Id,item_cal_name) values('3ABF19999C9D472992DAF2D0E43B662F','cal_name1');
运行到这里底下就不能执行了 违反唯一约束
各位老大给看看 谢了
insert into t_item_cal(item_cal_Id,item_cal_name) values('3ABF19999C9D472992DAF2D0E43B662F','cal_name2');
insert into t_item_cal(item_cal_Id,item_cal_name) values('3ABF19999C9D472992DAF2D0E43B662F','cal_name3');
insert into t_item_cal(item_cal_Id,item_cal_name) values('E852EEF21E794BB996C04111F85CEA4C','cal_name4');
insert into t_item_cal(item_cal_Id,item_cal_name) values('E852EEF21E794BB996C04111F85CEA4C','cal_name5');
insert into t_item_cal(item_cal_Id,item_cal_name) values('47802BD00A6F4FF29B1A1D83CB12DC86','cal_name6');
百度了一下需要加 unique 但是加了的话 那字表中不是只能有一个这样的主键了么 该怎么办
代码如下
create table t_item
(
item_id varchar2(32) not null primary key,
item_name varchar2(32),
item_cal_Id varchar2(32) unique,
item_kal_Id varchar2(32) unique
);create table t_item_cal
(
item_cal_Id varchar2(32) not null primary key,
item_cal_name varchar2(32),
constraint fk_item_cal foreign key(item_cal_Id) references t_item(item_cal_Id)
);create table t_item_kal
(
item_kal_Id varchar2(32) not null primary key,
item_kal_name varchar2(32) ,
constraint fk_item_kal foreign key(item_kal_Id) references t_item(item_kal_Id)
);insert into t_item(item_id,item_name,item_cal_Id,item_kal_Id) values (sys_guid(),'name1',sys_guid(),sys_guid());
insert into t_item(item_id,item_name,item_cal_Id,item_kal_Id) values (sys_guid(),'name2',sys_guid(),sys_guid());
insert into t_item(item_id,item_name,item_cal_Id,item_kal_Id) values (sys_guid(),'name3',sys_guid(),sys_guid());
insert into t_item(item_id,item_name,item_cal_Id,item_kal_Id) values (sys_guid(),'name4',sys_guid(),sys_guid());
insert into t_item(item_id,item_name,item_cal_Id,item_kal_Id) values (sys_guid(),'name5',sys_guid(),sys_guid());insert into t_item_cal(item_cal_Id,item_cal_name) values('3ABF19999C9D472992DAF2D0E43B662F','cal_name1');
运行到这里底下就不能执行了 违反唯一约束
各位老大给看看 谢了
insert into t_item_cal(item_cal_Id,item_cal_name) values('3ABF19999C9D472992DAF2D0E43B662F','cal_name2');
insert into t_item_cal(item_cal_Id,item_cal_name) values('3ABF19999C9D472992DAF2D0E43B662F','cal_name3');
insert into t_item_cal(item_cal_Id,item_cal_name) values('E852EEF21E794BB996C04111F85CEA4C','cal_name4');
insert into t_item_cal(item_cal_Id,item_cal_name) values('E852EEF21E794BB996C04111F85CEA4C','cal_name5');
insert into t_item_cal(item_cal_Id,item_cal_name) values('47802BD00A6F4FF29B1A1D83CB12DC86','cal_name6');
解决方案 »
- oracle number 小数位最后的0会被自动去掉? 应该如何不让他自动去掉后面的0 如: number(6,2) 插入0.00
- 求个sql,获取10分钟内唯一的数据
- ORACLE 链接SQLSERVER
- 一条sql语句的优化,急待解决?
- AdoDataSet.SaveToFile 怎么样可以保存成txt格式的.
- 删除数据库用户问题
- sqlldr导入能否挑字段
- oracle PROC编译程序出错,请高手指点!!!!!!!!!!!在线等待
- 急!!关于游标判断重复记录是否插入的问题,请大侠帮助!
- TOAD(蛤蟆)的一个问题
- oracle11g提供的em怎么查看表数据?
- 移植问题,Oracle的MAX,MIN函数(从DB2到Oracle)
当你运行下面这句sql,也就是你出现问题的SQL时
insert into t_item_cal(item_cal_Id,item_cal_name) values('3ABF19999C9D472992DAF2D0E43B662F','cal_name1');
必须保证在t_item表中存在某条记录,该记录所对应的列item_kal_Id值为:3ABF19999C9D472992DAF2D0E43B662F
否则就会出现违反外键约束的错误,不知道lz是否能够理解!多看书,万丈高楼平地起!
否则就会出现违反外键约束的错误,不知道lz是否能够理解!多看书,万丈高楼平地起!------------------------------------
不好意思,上面有点错误
(
item_kal_Id varchar2(32) not null primary key,
--看看这里是什么意思 主键是唯一的 而你下面插入记录的时候 item_kal_Id 有重复的 ,解决部分就是 在插入item_kal_Id的记录要唯一
item_kal_name varchar2(32) ,
constraint fk_item_kal foreign key(item_kal_Id) references t_item(item_kal_Id)
);
insert into t_item_cal(item_cal_Id,item_cal_name) values('3ABF19999C9D472992DAF2D0E43B662F','cal_name1');
insert into t_item_cal(item_cal_Id,item_cal_name) values('3ABF19999C9D472992DAF2D0E43B662F','cal_name2');
insert into t_item_cal(item_cal_Id,item_cal_name) values('3ABF19999C9D472992DAF2D0E43B662F','cal_name3');--红色标明的插入记录不唯一
建议去看下基础书籍 什么是主键什么是外键 什么是唯一
insert into t_item_cal(item_cal_Id,item_cal_name) values('E852EEF21E794BB996C04111F85CEA4C','cal_name4');
insert into t_item_cal(item_cal_Id,item_cal_name) values('E852EEF21E794BB996C04111F85CEA4C','cal_name5');
insert into t_item_cal(item_cal_Id,item_cal_name) values('47802BD00A6F4FF29B1A1D83CB12DC86','cal_name6');