How can I fix ORA-02270 error code in SQL?

Question

CREATE TABLE employee (
      empid        INT PRIMARY KEY,
      fname        VARCHAR2(50),
      lname        VARCHAR2(50)
);

CREATE TABLE manager (
      mgrid   INT
            REFERENCES employee ( empid )
);

CREATE TABLE barista (
      baristaid  INT
            REFERENCES employee ( empid ),
      mgrid      INT
            REFERENCES manager ( mgrid )
);

For barista table it gives me an error,

ORA-02270: no matching unique or primary key for this column-list
02270. 00000 -  "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
           gives a column-list for which there is no matching unique or primary
           key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
           catalog view

I also tried,

ALTER TABLE barista
      ADD CONSTRAINT fk_mgrid FOREIGN KEY ( mgrid )
            REFERENCES manager ( mgrid );

But I got the same error. Can you please tell me whats going wrong? Thank you in adavance.

Answer 1

Your immediate problem is that manager(mgrid) should be declared as a primary (or at least unique) key so that it is possible to reference it with a foreign key.

So you would need to change this:

CREATE TABLE manager (
      mgrid   INT 
            REFERENCES employee ( empid )
);

To:

CREATE TABLE manager (
      mgrid   INT primary key
            REFERENCES employee ( empid )
);

Wit this change, your code just works.