#1005 Can't create Table, Foreign Key Constraint is incorrectly formed

36 views Asked by At

I'm not a professional DB creator but I learned at my university the following code to create a table, needed for a many-to-many relationship.

CREATE TABLE mtm_table(
    user_id INT NOT NULL,
    goal_id INT NOT NULL,
    PRIMARY KEY (user_id, goal_id),
    FOREIGN KEY (user_id) REFERENCES user_table (user_id),
    FOREIGN KEY (goal_id) REFERENCES goal_table (goal_id)
    );

USER-Table:

    CREATE TABLE `user` (
  `user_id` int unsigned NOT NULL AUTO_INCREMENT,
  `fit_id` INTEGER NOT NULL,
  `email` varchar(255) NOT NULL,
  `user_passwort` varchar(255) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `birth_date` DATE NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`), 
  UNIQUE (`email`),
  FOREIGN KEY (fit_id) REFERENCES fit_level(fit_id)
);

GOAL-Table

    CREATE TABLE `goal` (
  `goal_id` int(11) NOT NULL,
  `fit_id` int(11) NOT NULL,
  `goal_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ITRA_points` int(11) DEFAULT NULL,
  `gps_data` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `distance` int(11) DEFAULT NULL,
  `incline` int(11) DEFAULT NULL,
  `alt_diff` int(11) DEFAULT NULL,
  `avg_diff` int(11) DEFAULT NULL,
  `asphalt` int(11) DEFAULT NULL,
  `gravel` int(11) DEFAULT NULL,
  `single_trail` int(11) DEFAULT NULL,
  `climbing` int(11) DEFAULT NULL,
  `time_beg` int(11) DEFAULT NULL,
  `time_adv` int(11) DEFAULT NULL,
  `time_pro` int(11) DEFAULT NULL,
  `poi_id` int(11) DEFAULT NULL,
  `geo_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Whenever i run that code it says that my foreign key constrait isn't formed properly, but that's exactly how i learned it. Are there any changes to the SQL syntax I don't know about?

King Regards

0

There are 0 answers