oracle 错误 ORA-02270: 此列列表没有匹配的唯一键或主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26429994/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
error ORA-02270: no matching unique or primary key for this column-list
提问by Sameer Azeem
I'm practicing a lab manual excercise in which I have to create 6 tables. Creation of 5 is successful.
我正在练习实验室手动练习，我必须在其中创建 6 个表。5创建成功。
But one line is giving error
但是一行给出了错误
constraint GRADE_Designation_FK
FOREIGN KEY(Designation) References EMPLOYEE(Designation),
ERROR at line 7:
第 7 行的错误：
ORA-02270: no matching unique or primary key for this column-list
ORA-02270: 此列列表没有匹配的唯一键或主键
Queries of 2 linked tables are
2个链接表的查询是
create table EMPLOYEE
(
Empno number(4) constraint EMPLOYEE_Empno_PK PRIMARY KEY,
Name varchar2(10) not null,
Designation varchar2(50),
Qualification varchar2(10),
Joindate date
);
create table GRADE
(
Designation varchar2(50) constraint GRADE_Designation_PK PRIMARY KEY,
Grade number(2),
TotalPosts number(4),
PostsAvailable number(4),
constraint GRADE_Grade_CK check(Grade between 1 and 20),
constraint GRADE_PostsAvailable_CK check(PostsAvailable <= TotalPosts),
constraint GRADE_Designation_FK FOREIGN KEY(Designation) References EMPLOYEE(Designation)
);
Tried
试过
create table GRADE
(
Designation varchar2(50) constraint GRADE_Designation_PK PRIMARY KEY,
Grade number(2),
TotalPosts number(4),
PostsAvailable number(4),
constraint GRADE_Grade_CK check(Grade between 1 and 20),
constraint GRADE_PostsAvailable_CK check(PostsAvailable <= TotalPosts)
);
create table EMPLOYEE
(
Empno number(4) constraint EMPLOYEE_Empno_PK PRIMARY KEY,
Name varchar2(10) not null,
Designation varchar2(50) NOT NULL UNIQUE,
Qualification varchar2(10),
Joindate date default sysdate
constraint EMPLOYEE_Designation_FK FOREIGN KEY(Designation) References GRADE(Designation),
);
Now new error
现在新的错误
constraint EMPLOYEE_Designation_FK FOREIGN KEY(Designation) References GRADE(Designation) *
ERROR at line 8:
ORA-02253: constraint specification not allowed here
约束 EMPLOYEE_Designation_FK FOREIGN KEY(Designation) 参考 GRADE(Designation) *
第 8 行的错误：
ORA-02253: 此处不允许约束规范
采纳答案by Bob Jarvis - Reinstate Monica
You've got the constraint on the wrong table. You should create a foreign key on EMPLOYEE.DESIGNATION, referencing back to GRADE.DESIGNATION.
你在错误的表上得到了约束。您应该在 EMPLOYEE.DESIGNATION 上创建一个外键，引用回 GRADE.DESIGNATION。
So your tables should look something like:
所以你的表应该看起来像：
create table GRADE
(
Designation varchar2(50) constraint GRADE_Designation_PK PRIMARY KEY,
Grade number(2),
TotalPosts number(4),
PostsAvailable number(4),
constraint GRADE_Grade_CK check(Grade between 1 and 20),
constraint GRADE_PostsAvailable_CK check(PostsAvailable <= TotalPosts),
);
create table EMPLOYEE
(
Empno number(4) constraint EMPLOYEE_Empno_PK PRIMARY KEY,
Name varchar2(10) not null,
Designation varchar2(50)
constraint EMPLOYEE_FK1
REFERENCES GRADE(DESIGNATION),
Qualification varchar2(10),
Joindate date
);
Share and enjoy.
分享和享受。
回答by Rahul
ORA-02270: no matching unique or primary key for this column-list
ORA-02270: 此列列表没有匹配的唯一键或主键
That error is very self explanatory and tells you what's wrong. In your case, you are trying to create foreign key on a non primary key column and so the error
该错误是不言自明的，并告诉您出了什么问题。在您的情况下，您正在尝试在非主键列上创建外键，因此错误
constraint GRADE_Designation_FK FOREIGN KEY(Designation)
References EMPLOYEE(Designation)
<--Here
Designationin EMPLOYEEtable is not a primary key and you can't create FK on a non primary key column. Your table creation rather should look like
Designationin EMPLOYEEtable 不是主键，您不能在非主键列上创建 FK。您的表格创建应该看起来像
create table GRADE
(
Designation varchar2(50) constraint GRADE_Designation_PK PRIMARY KEY,
employee_Empno number(4),
Grade number(2),
TotalPosts number(4),
PostsAvailable number(4),
constraint GRADE_Grade_CK check(Grade between 1 and 20),
constraint GRADE_PostsAvailable_CK check(PostsAvailable <= TotalPosts),
constraint GRADE_Designation_FK FOREIGN KEY(employee_Empno)
References EMPLOYEE(Empno));
回答by Pravin
It was the order of execution which has causes us this issue, make sure you have tables created with NOT NULL Enabled before adding those constraints to avoid this error.
这是导致我们出现此问题的执行顺序，请确保在添加这些约束之前创建了启用 NOT NULL 的表以避免此错误。
