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.