0

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

Akina
  • 39,301
  • 5
  • 14
  • 25
Quinix
  • 1
  • 1
  • Provide CREATE TABLE for user_table and goal_table. The query itself is legal. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5b48c36d871c93989ede4dc66be25248 Possible fail reason may be the difference in the datatype. Or maybe you use an engine which does not support foreign keys. – Akina May 11 '21 at 10:13
  • 2
    `INT` and `INT UNSIGNED` are different datatypes. They are not compatible in foreign key. – Akina May 11 '21 at 10:18
  • 1
    so I have to decide whether to take INT or INT unsigned on both tables right? – Quinix May 11 '21 at 10:24
  • This is correct. You must decide what datatype will be used for each pair. Separately - i.e. in one pair you may use unsigned whereas in another signed integer. To adapt to current state it is enough to make `mtm_table.user_id` unsigned. – Akina May 11 '21 at 10:25
  • for every primary key - foreign key relation, data types and lengths must be the same – Nasir Abbas May 11 '21 at 10:26
  • YES got it thank you so much! – Quinix May 11 '21 at 10:30

0 Answers0