SQLite Foreign Key Lack

I have looked through all the documentation about this and I cannot figure out what the problem is, maybe an experienced one can help me.

So, I create the LOCALIZACAO table:

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 is 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);

      

Although I am inserting items into the LOCALIZACAO table, when I am inserting into the SOCIOLOCALIZACAO table:

INSERT INTO SOCIOLOCALIZACAO VALUES (1,1);

      

I am given an error

foreign key mismatch "SOCIOLOCALIZACAO" referencing "LOCALIZACAO"

      

I am sure there is item 1 in SOCIO and 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!

+3


source to share


1 answer


Your foreign key refers to fields idS

and idL

in the table LOCALIZACAO

. There are no such fields, there is only a field id

.



I assume you only see this when inserting records because SQLite is not very pedantic. Other databases would not allow you to create such a foreign key.

+1


source







All Articles