CODEWITHSUNDEEP
sqldatabaseoracle-database
Sameer Azeem
Sameer Azeem

Reputation: 598

error ORA-02270: no matching unique or primary key for this column-list

I'm practicing a lab manual excercise in which I have to create 6 tables. Creation of 5 is successful.

But one line is giving error

constraint GRADE_Designation_FK 
   FOREIGN KEY(Designation) References EMPLOYEE(Designation),

ERROR at line 7:

ORA-02270: no matching unique or primary key for this column-list

Queries of 2 linked tables are

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

Upvotes: 3

Views: 12532

Answers (3)

Pravin Bansal
Pravin Bansal

Reputation: 4681

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.

Upvotes: 0

Rahul
Rahul

Reputation: 77876

ORA-02270: no matching unique or primary key for this column-list

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

Designation in EMPLOYEE table is not a primary key and you can't create FK on a non primary key column. Your table creation rather should 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));

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You've got the constraint on the wrong table. You should create a foreign key on EMPLOYEE.DESIGNATION, referencing back to 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.

Upvotes: 1

Related Questions