-2

I need to open table that represents what owner adopted what animal (the relation is one to many). I start with foreign keys and some columns but after I try to open that table, I get an error

This is the question from my test:

  • Primary Key is the combination of both ​owner_id​ and ​pet_id
  • owner_id​ references ​id ​from the ​owner​ table.
  • pet_id​ references ​id ​from the ​pet​ table. must be unique.
  • other columns: ​adoption_date

This is the script I made

CREATE TABLE `pet shelter`.`adoption` (
  `adoption_date` INT NULL,
  CONSTRAINT `owner_id`
    FOREIGN KEY ()
    REFERENCES `pet shelter`.`owner` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `pet_id`
    FOREIGN KEY ()
    REFERENCES `pet shelter`.`pet` ()
    ON DELETE CASCADE
    ON UPDATE CASCADE);

But I get this error:

Operation failed: There was an error while applying the SQL script to the database.

...followed by:

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') REFERENCES `pet shelter`.`owner` () ON DELETE NO ACTION ON UPDATE ' at line 4

0

Reference:

https://dev.mysql.com/doc/refman/8.0/en/create-table.html

No parentheses after in either of the places you used them.

[CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition

reference_definition:
    REFERENCES tbl_name (key_part,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

So:

FOREIGN KEY ()
    REFERENCES `pet shelter`.`owner` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

-->

FOREIGN KEY 
    REFERENCES `pet shelter`(owner)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.