Search code examples
mysqlsqlforeign-keyscascade

Why does MYSQL error when I try to add a foreign key?

I am trying to make a relational database in MYSQL. Currently, I am making the foreign keys and connecting them to the parent table. The problem is when I try to do this(in MYSQL workbench) MYSQL adds this line of code:

ADD INDEX `FK_party_coalitionparty_idx` (`partyId` ASC) VISIBLE;

After some research, I found out that it does this because when I delete or update the parent table, it is really handy when the child tables also delete or update the connected values(or columns).

The problem is when I run the foreign key code without the add index line it runs without trouble, but with it added (and I think I understand why it is good to have it added) it errors and does not want to execute the code to update my database.

When I try to execute the code in a SQL file it gives me the following error with the word VISIBLE:

VISIBLE is not valid at this position.

When I only try to delete the visible word, it cannot add my constraint (I think because you cannot put 2 times add below each other). I will include some screenshots and the message log to make my problem more clear.

Message log:

Operation failed: There was an error while applying the SQL script to the database.
Executing:
ALTER TABLE `testdatabase`.`coalitionparty` 
ADD INDEX `FK_party_coalitionparty_idx` (`partyId` ASC) VISIBLE;
;
ALTER TABLE `testdatabase`.`coalitionparty` 
ADD CONSTRAINT `FK_party_coalitionparty`
  FOREIGN KEY (`partyId`)
  REFERENCES `testdatabase`.`party` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

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 '' at line 2
SQL Statement:
ALTER TABLE `testdatabase`.`coalitionparty` 
ADD INDEX `FK_party_coalitionparty_idx` (`partyId` ASC) VISIBLE

My SQL File (when I try to run the SQL code but not with the workbench menu: SQL file with the error included at the bottom

The question: How do I need to fix this problem, so that I am able to use cascade and I don't get the error?

Thanks in advance!

Solution

  • Mariadb has no VISIBLE

    check the manual for more information

    So you can only do

    ALTER TABLE `coalitionparty` 
ADD INDEX `FK_party_coalitionparty_idx` (`partyId` ASC) ;

    or switch to MySsQL