Adding values to database but foreign key constraint failed

Question

I'm new to sqlite3 and I'm not so knowledgeable about foreign keys. I was trying to insert a data to User:

insert into User(User_id, name) values(201503426, 'John Doe');

But after I run it, I got foreign key constraint failed message.

So I have three tables:

CREATE TABLE `User` (
`User_id`   integer,
`plate_number`  integer DEFAULT 0,
`name`  text DEFAULT 'user',
`spot_number`   integer DEFAULT 0,
`credit`    integer DEFAULT 0,
`order_id`  integer DEFAULT 0,
FOREIGN KEY(`order_id`) REFERENCES `Orderr`(`order_id`),
FOREIGN KEY(`spot_number`) REFERENCES `Spot`(`spot_id`),
PRIMARY KEY(`User_id`)
);

CREATE TABLE `Spot` (
`spot_id`   integer,
`user_id`   text DEFAULT 'vacant',
`order_id`  integer DEFAULT 0,
`status`    integer DEFAULT 0,
FOREIGN KEY(`order_id`) REFERENCES `Orderr`(`order_id`),
FOREIGN KEY(`user_id`) REFERENCES `User`(`User_id`),
PRIMARY KEY(`spot_id`)
);

CREATE TABLE `Orderr` (
`order_id`  integer,
`user_id`   text DEFAULT 'user',
`spot_number`   integer DEFAULT 0,
`amount_due`    integer DEFAULT 0,
`time_lapse`    integer DEFAULT 0,
PRIMARY KEY(`order_id`),
FOREIGN KEY(`user_id`) REFERENCES `User`(`User_id`),
FOREIGN KEY(`spot_number`) REFERENCES `Spot`(`spot_id`)
);

How can I add the values without getting an error?.

Similar to: https://stackoverflow.com/questions/2965837/insert-statement-conflicted-with-the-foreign-key-constraint-sql-server, and https://dba.stackexchange.com/questions/46410/how-do-i-insert-a-row-which-contains-a-foreign-key — sɐunıɔןɐqɐp, Oct 19 '18 at 06:53

score 1 · Accepted Answer · answered Oct 19 '18 at 06:51

Here is the CREATE TABLE statement for your User table:

CREATE TABLE User (
    User_id integer,
    plate_number integer DEFAULT 0,
    name text DEFAULT 'user',
    spot_number integer DEFAULT 0,
    credit integer DEFAULT 0,
    order_id integer DEFAULT 0,
    FOREIGN KEY(order_id) REFERENCES Orderr(order_id),
    FOREIGN KEY(spot_number) REFERENCES Spot(spot_id),
    PRIMARY KEY(User_id)
);

Of importance here, for all columns other than User_id, you have default non NULL values defined. Now consider your insert statement:

INSERT INTO User (User_id, name)
VALUES
    (201503426, 'John Doe');

Because you did not specify the order_id column (as well as several others), therefore the default value will be inserted. So, your insert would result in the following record being added:

(User_id,   plate_number, name,       spot_number, credit, order_id)
(201503426, 0,            'John Doe', 0,           0,      0)

Now, because you have foreign key constraints in place on order_id and spot_number, SQLite will check to make sure that those corresponding tables actually have entires corresponding to the record you inserted. My guess is that such records do not exist, which is why you are seeing this error.

There are several ways to fix this, but the immediate change I would suggest would be to not use default values for your foreign key columns. Instead, allow them to be NULL. This way, you may do your current insert, and instead you would end up with the following record:

(User_id,   plate_number, name,       spot_number, credit, order_id)
(201503426, NULL,         'John Doe', NULL,        NULL,   NULL)

SQLite will allow a foreign key column to bear a NULL value and not point to its parent table. This would allow you to fill in the blanks later, perhaps when you have that information available.

Adding values to database but foreign key constraint failed

1 Answers1