-1

Let's say i have a table named borrows like this one:

CREATE TABLE IF NOT EXISTS `borrows` 
( `memberID` int(11) NOT NULL ,
 `ISBN` int(11) NOT NULL ,
 `CopyNr` int(11) NOT NULL ,
  `date_of_borrowing` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_of_reminder` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`memberID`,`ISBN`,`CopyNr`,`date_of_borrowing`) )
 ENGINE=InnoDB DEFAULT CHARSET=latin1

I want this table to have (ISBN,CopyNr) as a foreign key to an other table named copies.What i tried is this:

ALTER TABLE `borrows` ADD FOREIGN KEY (`ISBN`,`copyNr`) 
REFERENCES `copies`(`copyNr`) ON DELETE RESTRICT ON UPDATE RESTRICT;

This however gives me this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(`ISBN`),(`copyNr`)) REFERENCES `copies`(`copyNr`) ON DELETE RESTRICT ON UPDATE ' at line 1

Should i create another column that would contain tuples of(ISBN,CopyNr) first?If yes,how can this happen?If not,how can i solve this?

UPDATE:This is the code for copies:

CREATE TABLE IF NOT EXISTS `copies` 
( `copyNr` int(11) NOT NULL AUTO_INCREMENT,
 `shelf` text NOT NULL,
 `ISBN` int(11) NOT NULL,
 PRIMARY KEY (`copyNr`,`ISBN`) )
 ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1
Epitheoritis 32
  • 366
  • 5
  • 13
  • 1
    The foreign key must have the same number of columns (and same data types) as the primary key in the referenced table. You might like to read this checklist of foreign key requirements that I contributed to: https://stackoverflow.com/a/4673775/20860 – Bill Karwin Jun 03 '19 at 22:05
  • 1
    According to wikipedia, an INT would not be long enough to store ISBN numbers. – Uueerdo Jun 03 '19 at 22:21
  • @Uueerdo What type must it be?Should i make it a string? – Epitheoritis 32 Jun 03 '19 at 22:23
  • 1
    Wikipedia states they are 10 or 13 digits; INT would only cover 10 digit numbers starting with 2 or lower (and not all that start with 2), or 4 or lower if you used UNSIGNED (and not all starting with 4). BIGINT would work, but I'd probably just go with VARCHAR or CHAR; but I would probably give the `copies` table a surrogate auto-increment key to reference instead of using a composite one. – Uueerdo Jun 03 '19 at 22:30

1 Answers1

1
ALTER TABLE `borrows` ADD FOREIGN KEY (`ISBN`,`copyNr`) 
REFERENCES `copies`(`ISBN`,`copyNr`) ON DELETE RESTRICT ON UPDATE RESTRICT;

You should have composite index (ISBN, copyNr) on table copies

fifonik
  • 1,556
  • 1
  • 10
  • 18