0
Good afternoon I have an error of FK Ismatch on Sqlite that is already bothering me but I can not see the problem, follows the skeleton of the bank:
CREATE TABLE CEST_Item (
CIT_ID integer primary key
, CIT_COD numeric(4,1)
, CIT_Nome varchar
);
CREATE TABLE CEST_Segmento (
CSE_ID integer primary key
, CSE_COD varchar(3)
, CSE_Nome varchar
);
CREATE TABLE SistemaUnidade (
UNI_ID integer primary key
, UNI_UN varchar(20)
, UNI_Descricao varchar
);
CREATE TABLE NCM (
NCM_ID integer primary key
, NCM_COD varchar(10)
, NCM_Categoria varchar
, NCM_Descricao varchar
, NCM_IPI varchar(2)
, NCM_DataInicio Date
, NCM_Datafim Date
, UNI_ID integer
, NCM_GTINP Date
, NCM_GTINH Date
, NCM_OBS varchar
, foreign key(UNI_ID) references SistemaUnidade(UNI_ID)
);
CREATE TABLE CEST (
CEST_ID integer primary key
, CEST_COD varchar(9)
, NCM_COD varchar(10)
, CSE_COD varchar(3)
, CIT_ID integer
, CEST_Anexo varchar
, foreign key(NCM_COD) references NCM(NCM_COD)
, foreign key(CSE_COD) references CEST_Segmento(CSE_COD)
, foreign key(CIT_ID) references CEST_Item(CIT_ID)
);
When inserting data in the accessory tables everything happens normally without any problem, however when I try to do the following Insert gives the error of FK Mismatch:
INSERT INTO CEST VALUES(NULL, '01.001.00', '3815.12.10', '01', (SELECT CIT_ID FROM CEST_ITEM WHERE CIT_Nome like 'Catalisadores em colmeia cerâmica ou metálica para conversão catalítica de gases de escape de veículos e outros catalisadores'), '');
Yes, tables have values:
INSERT INTO CEST_Segmento VALUES(NULL, '01', 'Autopeças');
INSERT INTO CEST_Item VALUES(NULL, 1.0, 'Catalisadores em colmeia cerâmica ou metálica para conversão catalítica de gases de escape de veículos e outros catalisadores');
INSERT INTO NCM VALUES(NULL, '3815.12.10', 'PLÁSTICO E SUAS OBRAS', '3909.40.91 - FENOL-FORMALDEÍDO', '5', '42370', '', 4, '08/04/2019', '01/11/2018', '');
If I change the subquery for a fixed value occurs the same problem, this is the error message:
foreign key mismatch - "CEST" referencing "CEST_Segmento"
SQL Statement:
-- CEST
-- ID | COD | NCM | CODSEG | IDITEM | Anexo
INSERT INTO CEST VALUES(NULL, '01.001.00', '3815.12.10', '01', (SELECT CIT_ID FROM CEST_ITEM WHERE CIT_Nome like 'Catalisadores em colmeia cerâmica ou metálica para conversão catalítica de gases de escape de veículos e outros catalisadores'), '');.
Any idea?
Note that
foreign key(NCM_COD) references NCM(NCM_COD)
andforeign key(CSE_COD) references CEST_Segmento(CSE_COD)
do not reference a row uniquely determined in the target tables.– anonimo
@anonimo but according to the Sqlite website, the syntax is correct: https://www.sqlite.org/foreignkeys.html
CREATE TABLE track(
 trackid INTEGER, 
 trackname TEXT, 
 trackartist INTEGER,
 FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
What I would have to do differently then?– Mobius one
The syntax is correct, what is wrong is that the fields referenced in these two constraints are not primary keys or candidate keys, which is required in a Foreign key. See documentation: https://sqlite.org/foreignkeys.html
– anonimo