完整性约束(系统.FK_FACILITY) 已违反 - 找不到父密钥

Question

>我得到了"完整性约束(系统。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

我只想将值添加到表中，但我无法这样做...... 如果有人能帮助我，那就太好了！

Answer 1

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列缩短facility8 个字符
  • 或将该列放大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>

它有效。

---

最后，一个巨大的错误：

系统。FK_FACILITY

不要使用SYSTEM(也不SYS(用户进行数据库管理以外的任何事情。它们不适合用于像您这样的目的。