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- >boun...@mailinglists.sqlite.org] On Behalf Of Markos >Sent: Wednesday, 1 August, 2018 16:23 >To: sqlite-users@mailinglists.sqlite.org >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 >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
