why would the foreign keys giving me an error when the data types match

So I am trying to create these tables where the mechanics table would be able to pull the information when I use the JOIN SQL statement in terms of the carid.

Cars table

Mechanics table

CREATE TABLE cars(
carid INT (100) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
VIN INT (255) NOT NULL,
Make VARCHAR (100) NOT NULL,
Year Year(4) NOT NULL,
Color VARCHAR(10)
);
Create Table mechanic(
mechanicid INT (100) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL,
title VARCHAR(50) NOT NULL,
carid INT (100) NULL,
FOREIGN KEY(carid) REFERENCES cars(carid)
);

But I get this error

Error Code: 3780. Referencing column ‘carid’ and referenced column ‘carid’ in foreign key constraint ‘mechanic_ibfk_1’ are incompatible.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

INT UNSIGNED is not the same type as INT.

fiddle

Note that display widths for integer types don’t really do anything and are now deprecated. And INT (255) doesn’t make much sense anyway.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0