Some basics today - enforce relationship (Foreign key)

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

When you try adding fkey constraint in child table referencing a parent table using a field that is not primary key/unique constraint (ed) on the parent table this error occurs.

Example:

CREATE TABLE exp_pdoc
AS
   SELECT ROWNUM AS skey,
          person_doc_association_id,
          document_id,
          stateno,
          association_status_code,
          worker_region_id,
          association_date,
          dissassociation_date
     FROM person_doc_association
    WHERE 1 = 0;

ALTER TABLE exp_pdoc
ADD CONSTRAINT  pk_exp_pdoc PRIMARY KEY(skey);

CREATE TABLE exp_pdoc_attr
AS
   SELECT person_doc_association_id,
          ssn,
          last_name,
          first_name,
          date_of_birth,
          din,
          nysid,
          date_of_death,
          worker_region_id
     FROM sh_search_exp
    WHERE 1 = 0;

ALTER TABLE     exp_pdoc_attr
ADD CONSTRAINT  pk_exp_pdoc_attr PRIMARY KEY(person_doc_association_id);


ALTER TABLE     exp_pdoc_attr
ADD CONSTRAINT  fk_exp_pdoc_attr1 FOREIGN KEY(person_doc_association_id) REFERENCES exp_pdoc(person_doc_association_id);

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

Reason is in exp_pdoc, the field person_doc_association_id is not either primary key or even a unique constraint existed on it.


Remember a CHECK constraint is NOT going to help either. CHECK constraint cannot take subquery (only literal constants are allowed)

ALTER TABLE exp_pdoc_attr
add CONSTRAINT chk_exp_pdoc_attr
  CHECK (person_doc_association_id IN (
  select person_doc_association_id from exp_pdoc
  ));

ORA-02251: subquery not allowed here

So how to solve for this design question?

Scenario is this: i want to enforce in a second table that all values in a particular column (in fact its key but that may not be relevant) must be existent in a first table where the field is NOT the key, not even unique.

The tempting solution of having a 3rd table where the list is unique and refer to that in my second table is always there. Is that the best?

(Or) do i make the first table refer to the second table instead.. that is the more logical thing to do, because the second table is where the said column "person doc association id" IS UNIQUE, primary key infact. So that must be the referred to table for that field. YES.