Postgresql Error: "ERROR: there is no Unique Constraint matching Given Keys for referenced table "address""

Question

created the table Address and Client, as follows sql:

CREATE TABLE ENDERECO(
Logradouro VARCHAR (50) NOT NULL,
CEP VARCHAR(50) NOT NULL,
Numero INT NOT NULL,
Complemento VARCHAR(50),
Cidade VARCHAR (50) NOT NULL,
Bairro VARCHAR (50) NOT NULL,

PRIMARY KEY (Logradouro,CEP,Numero))


CREATE TABLE CLIENTE(
CPF INT NOT NULL,
Nome VARCHAR(50) NOT NULL,
TelefoneMovel VARCHAR(25) NOT NULL,
TelefoneFixo VARCHAR(25),
PRIMARY KEY (CPF))

Then I created the table Addressclient and gave this error

**ERROR: there is no Unique Constraint matching Given Keys for referenced table "address"

Follows the table:

CREATE TABLE ENDERECOCLIENTE(
ELogradouro VARCHAR (50) NOT NULL,
ECEP VARCHAR(50) NOT NULL,
ENumero INT NOT NULL,
CCPFCliente INT NOT NULL,

PRIMARY KEY (ELogradouro,ECEP,ENumero,CCPFCliente),

FOREIGN KEY (ELogradouro) REFERENCES ENDERECO(Logradouro),
FOREIGN KEY (ECEP) REFERENCES ENDERECO(CEP),
FOREIGN KEY (ENumero) REFERENCES ENDERECO(Numero),
FOREIGN KEY (CCPFCliente) REFERENCES CLIENTE(CPF))

What can it be? All columns being single and primary key?

Answer 1

The entities CLIENTE and ENDEREÇO relate.

The cardinality of this relationship is n:n, namely, a CLIENTE may possess none or several ENDEREÇOS registered, and in the same ENDEREÇO, may reside none or several CLIENTES different.

To represent relationships of cardinality n:n in relational databases a relationship table.

I suggest that your CLIENTES own a chave primária simple, based on a identificador único:

CREATE TABLE TB_ENDERECO
(
    id_endereco INT NOT NULL,
    Logradouro VARCHAR (50) NOT NULL,
    CEP VARCHAR(50) NOT NULL,
    Numero INT NOT NULL,
    Complemento VARCHAR(50),
    Cidade VARCHAR (50) NOT NULL,
    Bairro VARCHAR (50) NOT NULL,

    PRIMARY KEY (id_endereco)
);

Use the CPF of the customer as the chave primária is a good one when your CLIENTES they are natural persons:

CREATE TABLE TB_CLIENTE
(
    CPF VARCHAR(11) NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    TelefoneMovel VARCHAR(25) NOT NULL,
    TelefoneFixo VARCHAR(25),

    PRIMARY KEY (CPF)
);

So, your tabela de relacionamentos would look like this:

CREATE TABLE RL_ENDERECO_CLIENTE
(
    CPF VARCHAR(11) NOT NULL,
    id_endereco INTEGER NOT NULL,

    PRIMARY KEY (CPF,id_endereco),

    FOREIGN KEY (id_endereco) REFERENCES TB_ENDERECO (id_endereco),
    FOREIGN KEY (CPF) REFERENCES TB_CLIENTE (CPF)
);

What makes cardinality possible n:n desired:

--
-- EXEMPLO #1) JOAO E MARIA RESIDEM NO MESMO ENDERECO EM SAO PAULO:
--

-- CLIENTE
INSERT INTO TB_CLIENTE( CPF, Nome, TelefoneMovel, TelefoneFixo ) VALUES ( '12345678900', 'JOAO SILVA', '981-1234', '555-1234' );
INSERT INTO TB_CLIENTE( CPF, Nome, TelefoneMovel, TelefoneFixo ) VALUES ( '00011122299', 'MARIA SILVA', '981-0000', '555-1234' );

-- ENDERECO
INSERT INTO TB_ENDERECO ( id_endereco, Logradouro, CEP, Numero, Complemento, Cidade, Bairro ) VALUES ( 1, '', '', 123, '', 'SAO PAULO - SP', '' );

-- RELACIONAMENTOS
INSERT INTO RL_ENDERECO_CLIENTE ( CPF, id_endereco ) VALUES ( '12345678900', 1 );
INSERT INTO RL_ENDERECO_CLIENTE ( CPF, id_endereco ) VALUES ( '00011122299', 1 );

--
-- EXEMPLO #2) CLIENTE "MADALENA" POSSUI 2 ENDERECOS EM "BRASILIA-DF" E EM "PORTO ALEGRE-RS":
--

-- CLIENTE
INSERT INTO TB_CLIENTE( CPF, Nome, TelefoneMovel, TelefoneFixo ) VALUES ( '99988877700', 'MADALENA SANTOS', '981-2323', '555-0000' );

-- ENDERECOS
INSERT INTO TB_ENDERECO ( id_endereco, Logradouro, CEP, Numero, Complemento, Cidade, Bairro ) VALUES ( 2, '', '', 321, '', 'BRASILIA - DF', '' );
INSERT INTO TB_ENDERECO ( id_endereco, Logradouro, CEP, Numero, Complemento, Cidade, Bairro ) VALUES ( 3, '', '', 171, '', 'PORTO ALEGRE - RS', '' );

-- RELACIONAMENTOS
INSERT INTO RL_ENDERECO_CLIENTE ( CPF, id_endereco ) VALUES ( '99988877700', 2 );
INSERT INTO RL_ENDERECO_CLIENTE ( CPF, id_endereco ) VALUES ( '99988877700', 3 );

See an example working on SQL Fiddle.

Answer 2

CREATE TABLE ENDERECOCLIENTE(
ELogradouro VARCHAR (50) NOT NULL,
ECEP VARCHAR(50) NOT NULL,
ENumero INT NOT NULL,
CCPFCliente INT NOT NULL,

PRIMARY KEY (ELogradouro,ECEP,ENumero,CCPFCliente),

FOREIGN KEY (EEndereco) REFERENCES ENDERECO(Logradouro,CEP,Numero),
FOREIGN KEY (CCPFCliente) REFERENCES CLIENTE(CPF))

FK must "match" with their PK. I’d reverse your nomenclature pattern.