FK 和 PK 参考无效
当我标记所需表的主键引用时,无法创建表,因为 Oracle 显示“此列列表没有匹配的唯一键或主键”。
成功创建第一个表:
CREATE TABLE TEST
(
TESTno VARCHAR2(6) NOT NULL,
ExamNo VARCHAR2(6) NOT NULL,
TEST_Date DATE NOT NULL,
ACTUAL DATE,
PREDICTED Date,
CONSTRAINT TESTPKs PRIMARY KEY (TEST_Date, TESTno, ExamNo),
CONSTRAINT TTESTNO_Fk FOREIGN KEY (TESTno) REFERENCES TESTPAPER (Flightno)
CONSTRAINT TEXAMNo_FK FOREIGN KEY (ExamNo) REFERENCES Exam (ExamNo)
);
这是我想要创建的表,但出现错误:
CREATE TABLE Assignment
(
TEST_Date DATE NOT NULL,
ExamNo VARCHAR2(6) NOT NULL,
TestNo VARCHAR2(6) NOT NULL,
Type VARCHAR2(20),
Hours_Spent Decimal(4,2),
CONSTRAINT ASSIGNPKS PRIMARY KEY (TEST_Date, TestNo , ExamNo),
CONSTRAINT ASSIGNTESTDATE_FK FOREIGN KEY (TEST_Date) REFERENCES TEST(TEST_Date) ON
DELETE CASCADE,
CONSTRAINT ASSIGNTESTNO_FK FOREIGN KEY (TESTno) REFERENCES TESTPAPER (Flightno)
CONSTRAINT TEXAMNo_FK FOREIGN KEY (ExamNo) REFERENCES Exam (ExamNo)
);
我可以知道它一直没有给我匹配的唯一主键的问题出在哪里吗?我已经尝试重新创建并将“test_Date”标记为我的主键。但oracle好像找不到。
谢谢
Unable to create table as oracle shows ' no matching unique or primary key for this column-list' when I did label the primary key reference for the required table.
First table created successfully:
CREATE TABLE TEST
(
TESTno VARCHAR2(6) NOT NULL,
ExamNo VARCHAR2(6) NOT NULL,
TEST_Date DATE NOT NULL,
ACTUAL DATE,
PREDICTED Date,
CONSTRAINT TESTPKs PRIMARY KEY (TEST_Date, TESTno, ExamNo),
CONSTRAINT TTESTNO_Fk FOREIGN KEY (TESTno) REFERENCES TESTPAPER (Flightno)
CONSTRAINT TEXAMNo_FK FOREIGN KEY (ExamNo) REFERENCES Exam (ExamNo)
);
Here's the table i want to create and gives me error:
CREATE TABLE Assignment
(
TEST_Date DATE NOT NULL,
ExamNo VARCHAR2(6) NOT NULL,
TestNo VARCHAR2(6) NOT NULL,
Type VARCHAR2(20),
Hours_Spent Decimal(4,2),
CONSTRAINT ASSIGNPKS PRIMARY KEY (TEST_Date, TestNo , ExamNo),
CONSTRAINT ASSIGNTESTDATE_FK FOREIGN KEY (TEST_Date) REFERENCES TEST(TEST_Date) ON
DELETE CASCADE,
CONSTRAINT ASSIGNTESTNO_FK FOREIGN KEY (TESTno) REFERENCES TESTPAPER (Flightno)
CONSTRAINT TEXAMNo_FK FOREIGN KEY (ExamNo) REFERENCES Exam (ExamNo)
);
May i know where's the issue that it keeps giving me no matching unique primary keys? I already tried to recreate and labelled the 'test_Date' as my primary key. But oracle can't seems to find.
Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您引用的 PK 是
PRIMARY KEY (TEST_Date, TESTno, ExamNo)
— 因此外键也应该是FOREIGN KEY (TEST_Date, TESTno, ExamNo)
。您收到的错误是由于您尝试引用TEST
的 PK 的一部分造成的。另请参见 http://download.oracle.com/docs /cd/B10500_01/server.920/a96524/c22integ.htm
The PK you refer to is
PRIMARY KEY (TEST_Date, TESTno, ExamNo)
— hence the foreign key should beFOREIGN KEY (TEST_Date, TESTno, ExamNo)
as well. The error you're getting is due to your attempt to refer to a part ofTEST
's PK.See also http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c22integ.htm
检查您在外键中引用的表。这些列必须是主键或者在外表上是唯一的。
Check the tables you are referencing in your foreign keys. Those columns must be the primary key or otherwise unique on the foreign table.