FOREIGN KEY and CHECK constraint failures-CodePudding

I am trying to create an SQLite3 table that checks any inserted values in 'gameLicense' to conform to 3 checks: that the license_id entered is 5 digits; the first 4 digits are integers from 0 to 9, and that the last digit is equal to the last number produced by the calculation (1 * digit_1 3 * digit_2 1 * digit_3 3 * digit_4).

I have written the code below, which seems to have no syntax errors, but when I attempt to populate the tables with valid data the check constraint fails, and a later foreign key referencing 'license_id' fails as well (due to the check failure in this table?)

CREATE TABLE GameLicense (
title TEXT,
release_year INTEGER,
platform TEXT,
license_id TEXT PRIMARY KEY,
CHECK (length(license_id) = 5),
CHECK (CAST(substr(license_id,1) AS INTEGER) BETWEEN 0 AND 9),
CHECK (CAST (substr(license_id, 5) AS UNSIGNED) = 
substr(CAST(substr(license_id,1) AS UNSIGNED)   
3 * CAST(substr(license_id,2) AS UNSIGNED)   
CAST(substr(license_id,3) AS UNSIGNED)   
3 * CAST(substr(license_id,4) AS UNSIGNED), -1))
);

CREATE TABLE gameRental (
gamer_id INTEGER,
license_id TEXT,
date_out TEXT,
date_back TEXT,
rental_cost REAL,
FOREIGN KEY (license_id) REFERENCES GameLicense (license_id)
FOREIGN KEY (gamer_id) REFERENCES Gamer (gamer_id)
ON UPDATE CASCADE
);

Results in:

Error: near line 18: CHECK constraint failed: GameLicense
Error: near line 43: FOREIGN KEY constraint failed

Please ignore the line numbers, I cut out some other table creations that were irrelevant.

CodePudding user response：

substr(license_id,1) doesn't return the first character of the license ID, it returns the substring starting at position 1 up to the end of the string. Use

SUBSTR(license_id, 1, 1)

instead. Similarly in the other cases.