I have a table Cliente
, a table Cuenta
and a table Titular
of the following form:
CREATE TABLE Cliente(
DNI varchar(9) NOT NULL,
nombre varchar(50) NOT NULL,
apellidos VARCHAR(50) NOT NULL,
direccion VARCHAR(100) NOT NULL,
edad int NOT NULL,
email varchar(50) NOT NULL,
telefono int NOT NULL,
CONSTRAINT PK_Cliente PRIMARY KEY (DNI)
);
CREATE TABLE Cuenta(
IBAN varchar(24) NOT NULL,
numCuenta varchar(20) NOT NULL,
saldo decimal(15,2) default(0.00) check(saldo >= 0) NOT NULL,
fechaCreacion date NOT NULL,
CONSTRAINT PK_Cuenta PRIMARY KEY ( IBAN )
);
CREATE TABLE Cuenta_Corriente(
codOf int REFERENCES Oficina ON DELETE CASCADE,
CONSTRAINT PK_Cuenta_Corriente PRIMARY KEY ( IBAN )
) INHERITS (Cuenta);
CREATE TABLE Titular(
cliente varchar(9) REFERENCES Cliente ON DELETE CASCADE,
IBAN varchar(24) REFERENCES Cuenta ON DELETE CASCADE,
CONSTRAINT PK_Titular PRIMARY KEY (cliente,IBAN)
);
I want to perform the following INSERTS:
INSERT INTO Cliente (DNI, nombre, apellidos, direccion, edad, email, telefono) VALUES ('15013567H','Isaac','Lorente Muniesa','Ronda Hornillo 904',77,'[email protected]',666666666);
INSERT INTO Cuenta_Corriente(IBAN, numCuenta, fechaCreacion, codOf) VALUES ('ES7001145541321975169873', 01145541321975169873, '2017-02-18', 7496);
INSERT INTO Titular (Cliente, IBAN) VALUES ('15013567H', 'ES7001145541321975169873');
The problem is that when inserting in the Holder table I get the following error: ERROR: insert or update on table "titular" violates foreign key constraint "titular_iban_fkey" DETAIL: Key (iban)=(ES7001145541321975169873) is not present in table "cuenta".
And I don't understand why it is if that account already exists in the table with that same IBAN.
By definition in the official documentation , you cannot use child tables and foreign keys at the same time .
So either you use a constraint trigger that validates every time the parent table is called and avoid using FK, or you create a trigger function that validates if the ID exists in the child table.
The why goes through the trouble of implementing an efficient alternative that looks in N child tables to see if that value exists.
As a personal opinion, that OOP approach to tables is horrible (and so it dies in newer versions of PGSQL) :)
More ideas in this question