sql - 违反完整性约束 (SYSTEM.FK_FACILITY) - 未找到父键
问题描述
我收到“违反完整性约束 (SYSTEM.FK_FACILITY) - 未找到父键”错误,尽管主键已在上表中明确定义。
CREATE TABLE Facility (
facNo CHAR(11) NOT NULL,
facName VARCHAR(30) NOT NULL,
CONSTRAINT FacilityPK PRIMARY KEY (facNo)
);
INSERT INTO Facility (facNo, facName) VALUES ('F100', 'Football stadium');
INSERT INTO Facility (facNo, facName) VALUES ('F101', 'Basketball arena');
CREATE TABLE Customer (
custNo CHAR(8) NOT NULL,
custName VARCHAR(30) NOT NULL,
address VARCHAR(50) NOT NULL,
internal CHAR(10) NOT NULL,
city VARCHAR(30) NOT NULL,
zip VARCHAR(10) NOT NULL,
state1 VARCHAR(2) NOT NULL,
contact VARCHAR(50) NOT NULL,
phone VARCHAR(30) NOT NULL,
CONSTRAINT CustomerPk PRIMARY KEY (custNo)
);
INSERT INTO Customer (custNo, custName, address, internal, city, zip, state1, contact, phone) VALUES
('C100', 'Football', 'Box 352200', 'True', 'Boulder', '80309', 'CO', 'Mary Manager', '6857100');
INSERT INTO Customer (custNo, custName, address, internal, city, zip, state1, contact, phone) VALUES
('C101', 'Men''s Basketball', 'Box 352400', 'TRUE' , 'Boulder', '80309', 'CO', 'Sally Supervisor', '5431700');
CREATE TABLE EventReq (
evntNo CHAR(8) NOT NULL,
custNo CHAR(8) NOT NULL,
facNo CHAR(8) NOT NULL,
dateHeld DATE NOT NULL,
dateReq DATE NOT NULL,
dateAuth DATE,
status CHAR(20) NOT NULL,
estCost DECIMAL(8, 2) NOT NULL,
estAudience INTEGER NOT NULL,
budNo CHAR(30),
CONSTRAINT EventReqtPK PRIMARY KEY (evntNo),
CONSTRAINT FK_Facility FOREIGN KEY (facNo) REFERENCES Facility (facNo),
CONSTRAINT FK_Customer FOREIGN KEY (custNo) REFERENCES Customer (custNo)
);
INSERT INTO EventReq (evntNo, custNo, facNo, dateHeld, dateReq, dateAuth, status, estCost, estAudience, budNo)
VALUES ('E100', 'C100', 'F100', TO_DATE('2013-10-25','YYYY-MM-DD'), TO_DATE('2013-06-06','YYYY-MM-DD'), TO_DATE('2013-06-08','YYYY-MM-DD'), 'Approved', 5000.00, 80000, 'B1000');
我的确切错误是
INSERT INTO EventReq (evntNo, custNo, facNo, dateHeld, dateReq, dateAuth, status, estCost, estAudience, budNo)
VALUES ('E100', 'C100', 'F100', TO_DATE('2013-10-25','YYYY-MM-DD'), TO_DATE('2013-06-06','YYYY-MM-DD'), TO_DATE('2013-06-08','YYYY-MM-DD'), 'Approved', 5000.00, 80000, 'B1000')
Error report -
ORA-02291: integrity constraint (SYSTEM.FK_FACILITY) violated - parent key not found
我只想将价值添加到表格中,但我无法这样做......如果有人可以帮助我,那就太好了!
解决方案
CHAR
是一个糟糕的选择,除非您知道所有值都是固定长度的。为什么?因为 Oracle 用空格右填充这些值,直到最大列长度。
这就是你所做的:
SQL> desc facility;
Name Null? Type
----------------------------------------- -------- ----------------------------
FACNO NOT NULL CHAR(11) --> 11 characters
FACNAME NOT NULL VARCHAR2(30)
SQL> select facno, length(facno) lenfac, 'x' || facno ||'x' xfac from facility;
FACNO LENFAC XFAC
----------- ---------- -------------
F100 11 xF100 x
F101 11 xF101 x
-------
1234567 spaces
SQL>
看?
- 列长 = 11
- 你存储的价值是
F100
- 存储为
F100
+7个空格,共11个字符
插入eventreq
表格时不会出现问题,但是 - 你说它接受 11 个字符到facno
列中:
SQL> desc eventreq
Name Null? Type
----------------------------------------- -------- ----------------------------
EVNTNO NOT NULL CHAR(8)
CUSTNO NOT NULL CHAR(8)
FACNO NOT NULL CHAR(8) --> 8 characters
DATEHELD NOT NULL DATE
DATEREQ NOT NULL DATE
DATEAUTH DATE
STATUS NOT NULL CHAR(20)
ESTCOST NOT NULL NUMBER(8,2)
ESTAUDIENCE NOT NULL NUMBER(38)
BUDNO CHAR(30)
SQL>
这意味着 - 当您在表格中插入一行时eventreq
,该表格facno = F100
右侧填充了最多为列长度的空格,此处为8个字符(即F100
+ 4 个空格)。
比较:
facility - facno char(11)
eventreq - facno char(8)
F100 右填充 8 个字符与 F100 右填充 11 个字符不同,这就是 Oracle 说找不到匹配的父键的原因。
该怎么办?
facno
将表中的列缩短facility
为 8 个字符- 或将该列放大
eventreq
到 11 个字符
- 或将该列放大
- 将数据类型更改为
VARCHAR2
例如,如果两列都是CHAR(8)
:
SQL> desc facility
Name Null? Type
----------------------------------------- -------- ----------------------------
FACNO NOT NULL CHAR(8)
FACNAME NOT NULL VARCHAR2(30)
SQL> select * From facility;
FACNO FACNAME
-------- ------------------------------
F100 Football stadium
SQL> desc eventreq;
Name Null? Type
----------------------------------------- -------- ----------------------------
EVNTNO NOT NULL CHAR(8)
CUSTNO NOT NULL CHAR(8)
FACNO NOT NULL CHAR(8)
DATEHELD NOT NULL DATE
DATEREQ NOT NULL DATE
DATEAUTH DATE
STATUS NOT NULL CHAR(20)
ESTCOST NOT NULL NUMBER(8,2)
ESTAUDIENCE NOT NULL NUMBER(38)
BUDNO CHAR(30)
SQL> INSERT INTO EventReq (evntNo, custNo, facNo, dateHeld, dateReq, dateAuth, status, estCost, estAudience, budNo)
2 VALUES ('E100', 'C100', 'F100', TO_DATE('2013-10-25','YYYY-MM-DD'), TO_DATE('2013-06-06','YYYY-MM-DD'), TO_DATE('2013-06-08','YYYY-MM-DD'), 'Appr
oved', 5000.00, 80000, 'B1000');
1 row created.
SQL>
它有效。
最后,一个巨大的错误:
SYSTEM.FK_FACILITY
除了数据库管理之外,不要使用SYSTEM
(也不)用户。SYS
它们不适合用于像您这样的目的。
推荐阅读
- php - BadMethodCallException on laravel authentication
- testing - should E2E be run in production
- wpf - WPF - HorizontalAlignment="Right" does nothing
- amazon-sagemaker - SageMaker Estimator fit job never ends
- spring-data-jpa - 使用 queryDSL 和 Spring Data JPA 查询同一属性的多个值
- kubernetes - 在不破坏集群的情况下更改容器运行时
- vue.js - v-for:数组元素和属性的解构
- python - matplotlib:插入轴的事件处理和导航
- sql - 日期转换问题 ORACLE SQL
- excel - 在锁定单元格上粘贴excel中的值