ORA-02270 error: no matching unique or primary key for this column list
I am doing a lab tutorial in which I need to create 6 tables. Making 5 successful.
But one line gives an error
constraint GRADE_Designation_FK
FOREIGN KEY(Designation) References EMPLOYEE(Designation),
ERROR on line 7:
ORA-02270: no matching unique or primary key for this list of columns
Queries from 2 related tables:
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 a new mistake
Constraint EMPLOYEE_Designation_FK FOREIGN KEY (Designation) References GRADE (Designation) *
ERROR on line 8:
ORA-02253 constraint specification not allowed here
source to share
You have a limitation on the wrong table. You have to create a foreign key to EMPLOYEE.DESIGNATION by referencing GRADE.DESIGNATION.
So your tables should look something like this:
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.
source to share
ORA-02270: no matching unique or primary key for this list of columns
This error is very self-explanatory and tells you what is wrong. In your case you are trying to create a foreign key on a non-primary key column and so the error is
constraint GRADE_Designation_FK FOREIGN KEY(Designation)
References EMPLOYEE(Designation)
<--Here
Designation
in the table is EMPLOYEE
not a primary key and you cannot create an FK on a non-primary key column. The table creation should rather look like
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));
source to share