CODEWITHSUNDEEP
sqliteforeign-keysforeign-key-relationship
Pedro Pereira
Pedro Pereira

Reputation: 55

Foreign key mismatch SQLite

I searched all the documentation about this and I can't seem to understans what's the problem, maybe an experienced could help me.

So I'm creating a table called LOCALIZACAO:

CREATE TABLE LOCALIZACAO(
id INTEGER PRIMARY KEY AUTOINCREMENT,
rua TEXT NOT NULL,
codigoPostal TEXT NOT NULL,
UNIQUE (codigoPostal));

And this other table that references LOCALIZACAO, called SOCIOLOCALIZACAO:

CREATE TABLE SOCIOLOCALIZACAO(
idS INTEGER REFERENCES SOCIO(idS),
idL INTEGER NOT NULL REFERENCES LOCALIZACAO(idL),
CONSTRAINT pk_SOCIOLOCALIZACAO PRIMARY KEY (idS),
CONSTRAINT fk_SOCIOLOCALIZACAO FOREIGN KEY (idL) REFERENCES LOCALIZACAO(id) ON DELETE CASCADE ON UPDATE CASCADE);

Altough I'm inserting elements on the LOCALIZACAO table, when I'm inserting on the SOCIOLOCALIZACAO table:

INSERT INTO SOCIOLOCALIZACAO VALUES (1,1);

I'm given an error 

foreign key mismatch "SOCIOLOCALIZACAO" referencing "LOCALIZACAO"

I'm sure there is the element 1 in SOCIO and in LOCALIZACAO

Here is the SOCIO table:

CREATE TABLE SOCIO(
id INTEGER PRIMARY KEY AUTOINCREMENT,
nome TEXT NOT NULL,
anoDeNascimento INTEGER NOT NULL,
numeroDeSocio INTEGER NOT NULL CHECK (numeroDeSocio > 0),
nif INTEGER NOT NULL CHECK (nif > 99999999 AND nif < 1000000000),
anoDeVinculacao INTEGER NOT NULL CHECK (anoDeNascimento < anoDeVinculacao),
UNIQUE (nome, nif),
UNIQUE (numeroDeSocio));

Any help is appreciated! Thank you!

Upvotes: 3

Views: 295

Answers (1)

zmbq
zmbq

Reputation: 39013

Your foreign key references the fields idS and idL in table LOCALIZACAO. There are no such fields, there's only an id field there.

I guess you're only seeing this when inserting records because SQLite is not very pedantic. Other databases wouldn't have let you create such a foreign key.

Upvotes: 1

Related Questions