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