2

I am trying to create a new database with a hierarchical structure of two many-to-many tables using composite keys. Unfortunately the creation of tables fails stating that the foreign keys do not match:

Here a simplified Structure of the sql query:

CREATE TABLE a(
    a_ID serial PRIMARY KEY,
    a_name char(100)
);

CREATE TABLE b(
    b_ID serial PRIMARY KEY,
    b_name char(100)
);

CREATE TABLE c(
    c_ID serial PRIMARY KEY,
    c_name char(250),
);

-- First refering table
CREATE TABLE b_c(
    b_ID INT references b ON UPDATE CASCADE,
    c_ID INT references c ON UPDATE CASCADE,
    PRIMARY KEY (b_ID, c_ID)
);
-- Second higher level combining 
CREATE TABLE full(
    b_c_ID INT references b_c ON UPDATE CASCADE,
    a_ID INT references a ON UPDATE CASCADE,
    PRIMARY KEY (b_c_ID, a_ID)
);

The creation of the last table fails. Am I missing something here or is it impossible to refer to a composite primary key in just another composition? Any comments?

  • If a pk is composite (i.e. 2 columns), the foreign key constraint needs to have 2 columns as well. – ypercubeᵀᴹ Oct 12 '14 at 11:39
  • 1
    Irrelevant but you shouldn't name your table full. That's a SQL keyword and it will only give you trouble. – ypercubeᵀᴹ Oct 12 '14 at 11:41
  • Yes. Of course my names were just abbreviated examples – Curlew Oct 12 '14 at 11:59
  • Do you mind removing the "hierarchical" from the title? That usualy refers to special, self-referential design and related queries. It doesn't appear related to your case (you probably have an hierarchy of tables, as it is clear from the wording, i.e. first, second level - but that is not relevant to the question asked.) – ypercubeᵀᴹ Oct 12 '14 at 14:05
2

You need 2 columns in the foreign key constraint, to refer to a table with 2 columns in the primary key:

-- Second higher level combining 
CREATE TABLE full_b_c(
    b_ID INT NOT NULL,
    c_ID INT NOT NULL,
    a_ID INT NOT NULL references a ON UPDATE CASCADE,
    PRIMARY KEY (b_ID, c_ID, a_ID),
    FOREIGN KEY (b_ID, c_ID) 
      references b_c (b_ID, c_ID) ON UPDATE CASCADE
);
  • Great Thanks. And again I learned something new. – Curlew Oct 12 '14 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.