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?