0

I have two tables in a database, Library and Book. I'm trying to introduce a foreign key to Book's table in reference to the Library's name. Two tables with sample data

In essence, the "library" key under book should be a foreign key referencing the 'Library' Table's name attribute. I've tried doing this both during table creation and as an ALTER command with no success. Please take a look and let me know if you spot anything wrong.

TABLE CREATION COMMANDS USED: PlainText Code

CREATE TABLE`Library` (
  `name` VARCHAR(20) NOT NULL,
  `street` VARCHAR(50) NULL,
  `zip_Code` CHAR(5) NULL,
  `state` CHAR(2) NULL,
  PRIMARY KEY (`name`))
ENGINE = InnoDB;

CREATE TABLE `Book` (
  `isbn` CHAR(13) NOT NULL,
  `copy_number` TINYINT NOT NULL,
  `library` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`isbn`, `copy_number`, `library`),
  INDEX `library_idx` (`library` ASC) VISIBLE,
  CONSTRAINT `Book`
    FOREIGN KEY (`library`)
    REFERENCES `Library` (`name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

ERROR 1064 (42000): 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 '
  CONSTRAINT `library`
    FOREIGN KEY (`library`)
    REFERENCES `Library` ...' at line 6

ALTER USED: ALTER TABLE Book ADD CONSTRAINT FOREIGN KEY (library) REFERENCES Library(name);

Nicholas Chaudoir
  • 11
  • 2
  • 1
    Which SPECIFIC db are you using? MySQL and MariaDB are quite divergent now having forked 12 years ago, and also what db version? Can run a `SELECT version();` query to get the version. I'm thinking that the `VISIBLE` keyword may be the trouble. – Paul T. Oct 10 '21 at 01:03
  • This was correct, the VISIBLE keyword was causing syntax errors and removing it solved the issue. Apparently trying to forward engineer from MySQLWorkbench to an outdated version of MariaDB causes little errors like this one. – Nicholas Chaudoir Oct 10 '21 at 01:12
  • Yeah, that's the thing about MySQL workbench, though ... designed for MySQL but **not** for MariaDB -- as the two keep diverging, workbench will eventually become useless for MariaDB. – Paul T. Oct 10 '21 at 01:14
  • 1
    Yes, both MariaDB and MySQL have been gradually diverging since MariaDB forked in 2010. Don't think of these two products as compatible anymore. It's like Microsoft SQL Server vs. Sybase; those two products came from common code at some time in the distant past, but they have both changed a lot since then. – Bill Karwin Oct 10 '21 at 02:07

0 Answers0