Error: foreign key mismatch - "loan" referencing "user"

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Error: foreign key mismatch - "loan" referencing "user"

Markos
Hi,

I am a beginner in database and I'm developing a Database (with Tcl/Tk)
to control the loans of books in a reading room.

I created a table to register the users:

...

db1 eval {CREATE TABLE user (
                            id_user integer PRIMARY KEY,
                            name text NOT NULL,

                         ...

                         id_admin integer  NOT NULL
                            );}


Then create a table for the books:

db1 eval {CREATE TABLE book (
                            id_book integer PRIMARY KEY,
                            title text NOT NULL,

                     ...
                            subject text NOT NULL,
                            UNIQUE (id_book, title));}


And another table for each copy of the books, because we can have
different copies of a book:

db1 eval {CREATE TABLE copy (
                            id_copy integer PRIMARY KEY,
                            id_book integer NOT NULL,
                            copy_number integer NOT NULL,
                            ...
                            UNIQUE (id_book, copy_number),
                            FOREIGN KEY(id_book) REFERENCES book(id_book)
                            );}


And finally a table to register the loans with many FOREIGN KEYS:

db1 eval {CREATE TABLE loan (
                            id_loan integer PRIMARY KEY,
                            id_user integer NOT NULL,
                            id_book integer NOT NULL,
                            copy_number integer NOT NULL,
                            loan_date integer NOT NULL,
                            devolution_date integer NOT NULL,
                            ...
                            UNIQUE (id_user, id_book, copy_number,
loan_date),
                            FOREIGN KEY(id_book, copy_number) REFERENCES
copy(id_book, copy_number),
                            FOREIGN KEY(id_user) REFERENCES user(id_user),
                            FOREIGN KEY(id_admin_loan,
id_admin_devolution) REFERENCES user(id_user, id_user));}

But the command to insert a new loan return an error.

The command:

db1 eval "INSERT INTO loan (id_user, id_book, copy_number, loan_date,
devolution_date, ...) VALUES (1, 2, 1, 1532919600, 1514772000, ...)"

The error:

Error: foreign key mismatch - "loan" referencing "user"

foreign key mismatch - "loan" referencing "user"
foreign key mismatch - "loan" referencing "user"
     while executing
"db1 eval "INSERT INTO $table_name ($field_list) VALUES ($value_list)""

The insert command works well for insertion into the user, book, and
copy tables.

I'm just having error to insert into the loan table.

I can't find the reason for the error.

Any tip?

Thank you,

Markos


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Error: foreign key mismatch - "loan" referencing "user"

Clemens Ladisch
Markos wrote:
> CREATE TABLE user (
>     id_user integer PRIMARY KEY,
>     ...
> CREATE TABLE loan (
>     ...
>     FOREIGN KEY(id_admin_loan, id_admin_devolution) REFERENCES user(id_user, id_user)

I do not understand what this is trying to accomplish.
Why not two single-column FK constraints?

> Error: foreign key mismatch - "loan" referencing "user"

Because there is no unique index on (id_user,id_user).


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Error: foreign key mismatch - "loan" referencing "user"

Markos
In reply to this post by Markos
Hi Clemens,

The table user stores info about all users.

But some users have administrator privileges and can register a loan or
a devolution in the system.

On the line:
> FOREIGN KEY  (id_admin_loan, id_admin_devolution) REFERENCES user (id_user, id_user)

The id_admin_loan field is the id_user of a user who has adminstrator
privileges and registered a loan of a book.

The id_admin_devolution field is the id_user of a user with
administrator privilege and who registered the devolution of a book.

My intention is to register "who" did "what" in the system.

I followed your suggestion and separated the double constraint into two
separate single constraint:

FOREIGN KEY(id_admin_loan) REFERENCES user(id_user),

FOREIGN KEY(id_admin_devolution) REFERENCES user(id_user)

and worked fine. :-)

Thank you for the tip.

But I'm curious to understand why the original (double) restriction was
generating this error?

Because the other double constraint:

FOREIGN KEY(id_book, copy_number) REFERENCES copy(id_book, copy_number),

do not generate any error.

Thank you very much for your attention.

Markos



Markos wrote:

> CREATE TABLE user (
>      id_user integer PRIMARY KEY,
>      ...
> CREATE TABLE loan (
>      ...
>      FOREIGN KEY(id_admin_loan, id_admin_devolution) REFERENCES user(id_user, id_user)

I do not understand what this is trying to accomplish.
Why not two single-column FK constraints?

> Error: foreign key mismatch - "loan" referencing "user"

Because there is no unique index on (id_user,id_user).


Regards,
Clemens


Em 30-07-2018 20:42, Markos escreveu:

> Hi,
>
> I am a beginner in database and I'm developing a Database (with
> Tcl/Tk) to control the loans of books in a reading room.
>
> I created a table to register the users:
>
> ...
>
> db1 eval {CREATE TABLE user (
>                            id_user integer PRIMARY KEY,
>                            name text NOT NULL,
>
>                         ...
>
>                         id_admin integer  NOT NULL
>                            );}
>
>
> Then create a table for the books:
>
> db1 eval {CREATE TABLE book (
>                            id_book integer PRIMARY KEY,
>                            title text NOT NULL,
>
>                     ...
>                            subject text NOT NULL,
>                            UNIQUE (id_book, title));}
>
>
> And another table for each copy of the books, because we can have
> different copies of a book:
>
> db1 eval {CREATE TABLE copy (
>                            id_copy integer PRIMARY KEY,
>                            id_book integer NOT NULL,
>                            copy_number integer NOT NULL,
>                            ...
>                            UNIQUE (id_book, copy_number),
>                            FOREIGN KEY(id_book) REFERENCES book(id_book)
>                            );}
>
>
> And finally a table to register the loans with many FOREIGN KEYS:
>
> db1 eval {CREATE TABLE loan (
>                            id_loan integer PRIMARY KEY,
>                            id_user integer NOT NULL,
>                            id_book integer NOT NULL,
>                            copy_number integer NOT NULL,
>                            loan_date integer NOT NULL,
>                            devolution_date integer NOT NULL,
>                            ...
>                            UNIQUE (id_user, id_book, copy_number,
> loan_date),
>                            FOREIGN KEY(id_book, copy_number)
> REFERENCES copy(id_book, copy_number),
>                            FOREIGN KEY(id_user) REFERENCES user(id_user),
>                            FOREIGN KEY(id_admin_loan,
> id_admin_devolution) REFERENCES user(id_user, id_user));}
>
> But the command to insert a new loan return an error.
>
> The command:
>
> db1 eval "INSERT INTO loan (id_user, id_book, copy_number, loan_date,
> devolution_date, ...) VALUES (1, 2, 1, 1532919600, 1514772000, ...)"
>
> The error:
>
> Error: foreign key mismatch - "loan" referencing "user"
>
> foreign key mismatch - "loan" referencing "user"
> foreign key mismatch - "loan" referencing "user"
>     while executing
> "db1 eval "INSERT INTO $table_name ($field_list) VALUES ($value_list)""
>
> The insert command works well for insertion into the user, book, and
> copy tables.
>
> I'm just having error to insert into the loan table.
>
> I can't find the reason for the error.
>
> Any tip?
>
> Thank you,
>
> Markos
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Error: foreign key mismatch - "loan" referencing "user"

Keith Medcalf

Because the required unique index on copy(id_book, copy_number) exists (in the table definition).


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Markos
>Sent: Wednesday, 1 August, 2018 16:23
>To: [hidden email]
>Subject: Re: [sqlite] Error: foreign key mismatch - "loan"
>referencing "user"
>
>Hi Clemens,
>
>The table user stores info about all users.
>
>But some users have administrator privileges and can register a loan
>or
>a devolution in the system.
>
>On the line:
>> FOREIGN KEY  (id_admin_loan, id_admin_devolution) REFERENCES user
>(id_user, id_user)
>
>The id_admin_loan field is the id_user of a user who has adminstrator
>privileges and registered a loan of a book.
>
>The id_admin_devolution field is the id_user of a user with
>administrator privilege and who registered the devolution of a book.
>
>My intention is to register "who" did "what" in the system.
>
>I followed your suggestion and separated the double constraint into
>two
>separate single constraint:
>
>FOREIGN KEY(id_admin_loan) REFERENCES user(id_user),
>
>FOREIGN KEY(id_admin_devolution) REFERENCES user(id_user)
>
>and worked fine. :-)
>
>Thank you for the tip.
>
>But I'm curious to understand why the original (double) restriction
>was
>generating this error?
>
>Because the other double constraint:
>
>FOREIGN KEY(id_book, copy_number) REFERENCES copy(id_book,
>copy_number),
>
>do not generate any error.
>
>Thank you very much for your attention.
>
>Markos
>
>
>
>Markos wrote:
>
>> CREATE TABLE user (
>>      id_user integer PRIMARY KEY,
>>      ...
>> CREATE TABLE loan (
>>      ...
>>      FOREIGN KEY(id_admin_loan, id_admin_devolution) REFERENCES
>user(id_user, id_user)
>
>I do not understand what this is trying to accomplish.
>Why not two single-column FK constraints?
>
>> Error: foreign key mismatch - "loan" referencing "user"
>
>Because there is no unique index on (id_user,id_user).
>
>
>Regards,
>Clemens
>
>
>Em 30-07-2018 20:42, Markos escreveu:
>> Hi,
>>
>> I am a beginner in database and I'm developing a Database (with
>> Tcl/Tk) to control the loans of books in a reading room.
>>
>> I created a table to register the users:
>>
>> ...
>>
>> db1 eval {CREATE TABLE user (
>>                            id_user integer PRIMARY KEY,
>>                            name text NOT NULL,
>>
>>                         ...
>>
>>                         id_admin integer  NOT NULL
>>                            );}
>>
>>
>> Then create a table for the books:
>>
>> db1 eval {CREATE TABLE book (
>>                            id_book integer PRIMARY KEY,
>>                            title text NOT NULL,
>>
>>                     ...
>>                            subject text NOT NULL,
>>                            UNIQUE (id_book, title));}
>>
>>
>> And another table for each copy of the books, because we can have
>> different copies of a book:
>>
>> db1 eval {CREATE TABLE copy (
>>                            id_copy integer PRIMARY KEY,
>>                            id_book integer NOT NULL,
>>                            copy_number integer NOT NULL,
>>                            ...
>>                            UNIQUE (id_book, copy_number),
>>                            FOREIGN KEY(id_book) REFERENCES
>book(id_book)
>>                            );}
>>
>>
>> And finally a table to register the loans with many FOREIGN KEYS:
>>
>> db1 eval {CREATE TABLE loan (
>>                            id_loan integer PRIMARY KEY,
>>                            id_user integer NOT NULL,
>>                            id_book integer NOT NULL,
>>                            copy_number integer NOT NULL,
>>                            loan_date integer NOT NULL,
>>                            devolution_date integer NOT NULL,
>>                            ...
>>                            UNIQUE (id_user, id_book, copy_number,
>> loan_date),
>>                            FOREIGN KEY(id_book, copy_number)
>> REFERENCES copy(id_book, copy_number),
>>                            FOREIGN KEY(id_user) REFERENCES
>user(id_user),
>>                            FOREIGN KEY(id_admin_loan,
>> id_admin_devolution) REFERENCES user(id_user, id_user));}
>>
>> But the command to insert a new loan return an error.
>>
>> The command:
>>
>> db1 eval "INSERT INTO loan (id_user, id_book, copy_number,
>loan_date,
>> devolution_date, ...) VALUES (1, 2, 1, 1532919600, 1514772000,
>...)"
>>
>> The error:
>>
>> Error: foreign key mismatch - "loan" referencing "user"
>>
>> foreign key mismatch - "loan" referencing "user"
>> foreign key mismatch - "loan" referencing "user"
>>     while executing
>> "db1 eval "INSERT INTO $table_name ($field_list) VALUES
>($value_list)""
>>
>> The insert command works well for insertion into the user, book,
>and
>> copy tables.
>>
>> I'm just having error to insert into the loan table.
>>
>> I can't find the reason for the error.
>>
>> Any tip?
>>
>> Thank you,
>>
>> Markos
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users