Ошибка при выполнении SQL запроса к базе данных 'Cats bd': foreign key mismatch - "owners" referencing "cats"

Question

Вот при таком коде ошибка из заголовка при попытке выполнить последний запрос:

CREATE TABLE cats( id INTEGER PRIMARY KEY AUTOINCREMENT, 
                   Name TEXT, 
                   ownerID INTEGER, 
                   specie TEXT, 
                   birthday DATE);

CREATE TABLE owners( OwnerID INTEGER PRIMARY KEY AUTOINCREMENT, 
                     OwnerName TEXT, ownerSurname TEXT,
                     FOREIGN KEY (OwnerID) REFERENCES cats (OwnerID) );  

INSERT INTO cats( Name, ownerID, specie,  birthday) VALUES 
("Pushock", 1, "Scottish", 20-07-02 ), 
("Barsic", 2, "Siberian", 15-04-12), 
("Pascal", 2, "Siberian", 18-02-11),
("Floppa", 2, "Caracal", 2017-12-21);

INSERT INTO owners( OwnerName, ownerSurname)  VALUES  
("Vasiliy", "Kotov"), 
("Ivan", "Progerov");

Типы данных в обеих таблицах, вроде, совпадают. Как нормально связать эти две таблицы по ключам, чтобы не возникало такой ошибки?

Answer 1

CREATE TABLE owners ( OwnerID INTEGER PRIMARY KEY AUTOINCREMENT, 
                      OwnerName TEXT, 
                      OwnerSurname TEXT );

CREATE TABLE cats ( id INTEGER PRIMARY KEY AUTOINCREMENT, 
                    Name TEXT, 
                    ownerID INTEGER, 
                    specie TEXT, 
                    birthday DATE,
                    FOREIGN KEY (OwnerID) REFERENCES owners (OwnerID));

INSERT INTO owners( OwnerName, ownerSurname)  
VALUES  
("Vasiliy", "Kotov"), 
("Ivan", "Progerov");

INSERT INTO cats( Name, ownerID, specie,  birthday)  
SELECT "Pushock", OwnerID, "Scottish", '2020-07-02'
FROM owners
WHERE (OwnerName, ownerSurname) = ("Vasiliy", "Kotov");

INSERT INTO cats( Name, ownerID, specie,  birthday)  
SELECT Name, ownerID, specie,  birthday
FROM ( SELECT "Barsic" Name, "Siberian" specie, '2015-04-12' birthday UNION ALL
       SELECT "Pascal", "Siberian", '2018-02-11' UNION ALL
       SELECT "Floppa", "Caracal", '2017-12-21' ) cats 
CROSS JOIN ( SELECT OwnerID
             FROM owners
             WHERE (OwnerName, ownerSurname) = ("Ivan", "Progerov")) owners;

SELECT *
FROM owners 
JOIN cats USING (ownerID);

OwnerID	OwnerName	OwnerSurname	id	Name	specie	birthday
1	Vasiliy	Kotov	1	Pushock	Scottish	2020-07-02
2	Ivan	Progerov	2	Barsic	Siberian	2015-04-12
2	Ivan	Progerov	3	Pascal	Siberian	2018-02-11
2	Ivan	Progerov	4	Floppa	Caracal	2017-12-21

db<>fiddle here

БЛОГ НА HUSL

Ошибка при выполнении SQL запроса к базе данных 'Cats bd': foreign key mismatch - "owners" referencing "cats"

Ответы (1 шт):