PostgreSQL Error: "ERROR: there is no unique constraint matching given keys for referenced table" address ""

Question

PostgreSQL Error: "ERROR: there is no unique constraint matching given keys for referenced table" address ""

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I created the Address and Client table, 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 CustomerAddress table and gave this error

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

Follow 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? Are all columns unique and primary key?

sql postgresql

asked by anonymous 13.12.2017 / 03:53

2 answers

Problem sending input value (which is updated in a While) to another page via POST Feedback to the user

score 1 · Answer 1

The entities CLIENTE and ENDEREÇO relate.

The cardinality of this relationship is n:n , that is, a CLIENTE can have none or several ENDEREÇOS registered, and in a same ENDEREÇO , can reside none or multiple CLIENTES different.

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

I suggest that your table of CLIENTES has a simple chave primária , 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)
);

Using% of client as CPF is good when your chave primária is for individuals:

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 CLIENTES 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 the cardinality tabela de relacionamentos 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 in SQL Fiddle .

score 0 · 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))

The FK must "hit" the respective PK. I would review your naming pattern.