0

I am new to SQLite. I am on the latest version Version 3.36.0. And I am practicing foreign key constraints

I created two tables. Table 1 tracks files that have errors and what category the error falls under. Table 2 is tracks the errors and the error categories.

Table 1 Schema:

CREATE TABLE IF NOT EXISTS "ErrorLog_Items" (
    "Id"    INTEGER NOT NULL UNIQUE,
    "ToolType"      TEXT NOT NULL,
    "TdxFolderPath" TEXT NOT NULL,
    "TdxFileName"   TEXT NOT NULL UNIQUE,
    "LastTried"     TEXT NOT NULL,
    "Status"        TEXT NOT NULL,
    "ErrorType"     TEXT NOT NULL,
    "ErrorDetails"  TEXT NOT NULL,
    "RetryCount"    INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY("Id" AUTOINCREMENT)
);

Table 2 Schema:

CREATE TABLE Error_Catalog(
    Id INTEGER PRIMARY KEY,
    ErrorType TEXT NOT NULL,
    ErrorDetails TEXT NOT NULL,
    FOREIGN KEY (ErrorType) REFERENCES ErrorLog_Items (ErrorType)
    ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (ErrorDetails) REFERENCES ErrorLog_Items (ErrorDetails)
    ON UPDATE CASCADE ON DELETE CASCADE
);

I inserted the following into table 1:

INSERT INTO ErrorLog_Items (ToolType, TdxFolderPath, TdxFileName, LastTried, 
Status, ErrorType, ErrorDetails)
Values('CIM', '\\CIM\2021_11_22\10_07','A04_CIM_CIM101_Z12575BZ_2612_X29.xml.gz','2021-11-22 10:20:00', 
'Failed','non recoverable', 'Parsing of xml failed')

I inserted the following into table 2:

INSERT INTO Error_Catalog(ErrorType, ErrorDetails) Values
('recoverable','bad text');

I was expecting an error since both keys are not present in table 1. But did not. What did I do wrong?

The other question I have is, will table 2 be automatically populated by table 1 error type and errordetails value whenever I insert them into table 1? Or does that require a sql script to do it?

Slava Rozhnev
  • 8,085
  • 6
  • 21
  • 35
edo101
  • 613
  • 3
  • 14
  • 1
    If you want to learn SQL in general, you should consider another dbms. SQLite is a bit special in may ways. – jarlh Nov 22 '21 at 19:46

0 Answers0