FK Ismatch sqlite

Asked

Viewed 28 times

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) and foreign key(CSE_COD) references CEST_Segmento(CSE_COD) do not reference a row uniquely determined in the target tables.

  • @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?

  • 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

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.