1

I have a problem creating a table that contains a foreign key from another table that has two primary keys.

Below are the tables:

create table names_types(
id_name serial,name varchar(70),type varchar(70),primary key(id_name,name)
);

create table risk_final(
id_risk serial,id_date varchar(70)references date_final(vdate_id)
,name varchar(70)references names_types(name)
,var_mc_risk varchar(70)
,mvar_mc_risk varchar(70)
,incvar_mc_risk varchar(70)
,cvar_mc_risk varchar(70)
,delta varchar(70)
,present_value varchar(70)
,net_exposure varchar(70)
,amount varchar(70)
,primary key(id_risk,id_date,name)
);

The table risk_final can't be created and gives me the ERROR:

ERROR: there is no unique constraint matching given keys for referenced table "names_types"

How can I solve it?

Pang
  • 9,564
  • 146
  • 81
  • 122
M.Bennani
  • 31
  • 1
  • 5
  • 3
    Possible duplicate of [Postgres FK referencing composite PK](https://stackoverflow.com/questions/9984022/postgres-fk-referencing-composite-pk) – Heir Of Knowledge Dec 24 '17 at 13:02

1 Answers1

2

I think that the message is pretty clear: you cannot reference part of a key, either primary or candidate (hence "unique constraint"). If you need to reference the names_types table you could create a surrogate key on the latter table and reference this key (e.g. an integer id), but if you actually want to reference only the name column you should have a third table, e.g. names, with name as its primary key:

create table names (name varchar(70), primary key (name));
create table types (id_name serial, primary key (id_name));
create table names_types (id_name serial references types(id_name), 
                          name varchar(70) references names(name), 
                          primary key(id_name, name));
Giorgos Altanis
  • 2,742
  • 1
  • 13
  • 14
  • i don't quit understand you , i need to refernce only the name column ,, but tne name column can't be a primary key by itself, i need an id.what shall i do? – M.Bennani Mar 19 '17 at 20:58
  • `CREATE TABLE names (name varchar(70), primary key (name)` and then reference this table: `CREATE TABLE risk_final (..., name varchar(70) references names(name), ...` – Giorgos Altanis Mar 19 '17 at 21:01
  • i got your point , names (name varchar(70) primary key , type varchar(70)) ,but in this case name is unique for a type and i need in some cases to have multiple companies with same name that's why i need an id as a primary key with name "primary key(id,name)". help plz – M.Bennani Mar 19 '17 at 21:10
  • create table names (name varchar(70), primary key (name)); create table types (id_name serial, primary key (id_name)); create table names_types (id_name serial references types(id_name), name varchar(70) references names(name), primary key(id_name, name)); – Giorgos Altanis Mar 19 '17 at 21:16
  • As long as it is correct for your model... I updated the answer for completeness. – Giorgos Altanis Mar 19 '17 at 21:31
  • you don't quit understand me Sir, the table names in this case can't accept redendancy (the same name can't be inseted twice), wich is a problem for me since the insertion is generated automatically from xls files through Talend. name attribute must be coupled with an id in the same table – M.Bennani Mar 20 '17 at 21:47
  • No, `names` table should not have redundant entries, this is exactly why you should use it. If you want redundancy then you **cannot** have a foreign key constraint. So you get to choose: either you use a schema such as the one I suggested and you can have foreign key constraints, or you don't have foreign key constraints at all. – Giorgos Altanis Mar 20 '17 at 21:50