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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
