https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33905
--- Comment #30 from David Cook <[email protected]> --- CREATE TABLE test_identifiers ( borrowernumber int(11) NOT NULL, identifier varchar(75) NOT NULL, CONSTRAINT t_ids UNIQUE (identifier), PRIMARY KEY (borrowernumber,identifier) ); CREATE TABLE test_borrowers ( borrowernumber int(11) NOT NULL AUTO_INCREMENT, cardnumber varchar(32) DEFAULT NULL, userid varchar(75) DEFAULT NULL, CONSTRAINT t_cardnumber FOREIGN KEY (borrowernumber,cardnumber) REFERENCES test_identifiers (borrowernumber,identifier), CONSTRAINT t_userid FOREIGN KEY (borrowernumber,userid) REFERENCES test_identifiers (borrowernumber,identifier) ); insert into test_borrowers (cardnumber,userid) VALUES (null,null); insert into test_borrowers (cardnumber,userid) VALUES (null,null); insert into test_borrowers (cardnumber,userid) VALUES (null,null); -- I've made the SQL as simple as possible, so that you can easily try it out with some basic SQL in KTD. After running the above SQL, you can generate your identifiers: insert into test_identifiers (borrowernumber,identifier) VALUES (1,'test'); insert into test_identifiers (borrowernumber,identifier) VALUES (1,'test2'); update test_borrowers set userid = 'test' where borrowernumber = 1 update test_borrowers set cardnumber = 'test' where borrowernumber = 1 update test_borrowers set userid = 'test2' where borrowernumber = 1 You can see here that the userid and cardnumber can be the same or different. If you try to update borrowernumber 2 to use any of these identifiers, you'll find that it fails the DB constraint. -- You are receiving this mail because: You are watching all bug changes. _______________________________________________ Koha-bugs mailing list [email protected] https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
