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

Question

I have the following SQL code

CREATE TABLE EMPLOYEES
(
empID       NUMBER     NOT NULL,
ssn         CHAR(10)    NOT NULL,
fname       VARCHAR(20) NOT NULL,
minit       VARCHAR(15),
lname       VARCHAR(30) NOT NULL,
gender      CHAR(2),
email       VARCHAR(40),
street      VARCHAR(40),
postalCode  NUMBER,
city        VARCHAR(20),
country     VARCHAR(20),
job         VARCHAR(20),
salary      NUMBER(*,2),
birthdate   DATE,
specialization   VARCHAR(30),
username         VARCHAR(25),
password         VARCHAR(25),
levelOfClearance VARCHAR(20),

PRIMARY KEY (empID),
UNIQUE      (ssn)
);

CREATE TABLE PATIENTS
(
patientID   NUMBER NOT NULL,
healthInsID NUMBER,
fname       VARCHAR(20) NOT NULL,
minit       VARCHAR(15),
lname       VARCHAR(30) NOT NULL,
gender      CHAR(1),
email       VARCHAR(40),
street      VARCHAR(40),
postalCode  CHAR(4),
city        VARCHAR(20),
country     VARCHAR(20),

PRIMARY KEY  (patientID),
FOREIGN KEY  (healthInsID) REFERENCES HEALTH_INSURANCES (healthInsID)
ON DELETE SET NULL    
);

CREATE TABLE APPOINTMENTS
(
appointmentID NUMBER NOT NULL,
empID NUMBER NOT NULL,
appointmentDate DATE,
cost NUMBER(*,2),
patientID NUMBER,

PRIMARY KEY (appointmentID),
FOREIGN KEY (empID) REFERENCES EMPLOYEES (empID)
ON DELETE SET NULL,
FOREIGN KEY (patientID) REFERENCES PATIENTS
ON DELETE SET NULL
);

CREATE TABLE SYMPTOMS
(
appointmentID NUMBER  NOT NULL,
description VARCHAR(100),

PRIMARY KEY (appointmentID),
FOREIGN KEY (appointmentID) REFERENCES APPOINTMENTS (appointmentID)
ON DELETE SET NULL
);

The first 3 tables are created without a problem, but the last one give me the error "ORA-02270: no matching unique or primary key for this column-list". I have searched a lot but could not find any solution yet.

Have you seen [this](http://stackoverflow.com/questions/10802212/oracle-ora-02270-no-matching-unique-or-primary-key-for-this-column-list-erro)?? — huMpty duMpty, Jan 02 '14 at 09:49
Are you sure about this? I've tried to create the same tables as you (except the foreign key in table PATIENTS - you don't define HEALTH_INSURANCES table) and there was no problem at all. He're the fiddle: http://sqlfiddle.com/#!4/dc723/1/0 — vkamayiannis, Jan 02 '14 at 09:52

Rahul · Answer 1 · 2014-01-02T10:04:48.590

Not sure but I think the problem probably is with last line in your symptoms table

CREATE TABLE SYMPTOMS
(
appointmentID NUMBER  NOT NULL,  <-- it's NOT NULL
description VARCHAR(100),

PRIMARY KEY (appointmentID),
**FOREIGN KEY (appointmentID) REFERENCES APPOINTMENTS (appointmentID)
ON DELETE SET NULL** <-- here you are saying -- set to null on delete 
);

You are trying to null the appointmentID which you have declared as non null. This doesn't looks correct. I tried creating the same table with on delete cascade and it worked fine.

(OR)

Try creating your SYMPTOMS table like below

CREATE TABLE SYMPTOMS
(
appointmentID number  NOT NULL,
description VARCHAR(100),
FK_APT_APTID number null,  

PRIMARY KEY (appointmentID),
FOREIGN KEY (FK_APT_APTID) REFERENCES APPOINTMENTS (appointmentID)
ON DELETE set null
);

See this sample fiddle

http://sqlfiddle.com/#!3/826dd

@user3118957, then there is something you haven't mentioned. As per your post it should be fine. Provide more details in case you want us to help further. — Rahul, Jan 02 '14 at 10:15

score 0 · Answer 2 · answered Jan 02 '14 at 10:27

The problem is with the SYMPTOMS table, your PK can be set to null (which is not possible) I suggest you'll do the following:

Add a symptom ID column as a PK (or set the combination of description and appointmentID as PK)
Set on delete cascade or don't set a delete policy: this will cause an error if you delete an appointment and will raise an exception that you'll need to handle in your code.

sql fiddle: http://sqlfiddle.com/#!4/67d4b

CREATE TABLE SYMPTOMS
(
SymptomID     NUMBER NOT NULL,
appointmentID NUMBER  NOT NULL,
description VARCHAR(100),

PRIMARY KEY (appointmentID),
FOREIGN KEY (appointmentID) REFERENCES APPOINTMENTS (appointmentID)
);

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

2 Answers2