0

I have the following problem with a pretty simple MySQL database table creation:

SQL query:

CREATE TABLE IF NOT EXISTS comments(  
    commentId INT NOT NULL AUTO_INCREMENT,  
    comment VARCHAR(1024) NOT NULL,  
    commentdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    profileId INT NOT NULL,  
    PRIMARY KEY(commentId),  
    CONSTRAINT fk_ProfilesProfileId FOREIGN KEY(profileId) REFERENCES  profiles.profileId ON UPDATE RESTRICT ON DELETE CASCADE  
)Engine=InnoDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci

Error:

MySQL said: Documentation #1215 - Cannot add foreign key constraint

As you can see, the error number is 1215, which would indicate that the problem is about Foreign Key and Primary Key in comments and profiles tables (respectively) being different types. However, I have checked multiple times that they both are INT(11) and signed.

Here are queries for both of them:
'profiles' -table

CREATE TABLE IF NOT EXISTS profiles(
    profileId INT NOT NULL AUTO_INCREMENT,
    corpname VARCHAR(512) NOT NULL DEFAULT 'VIRHE',
    corpserial VARCHAR(16) NOT NULL DEFAULT 'VIRHE',
    loginusername VARCHAR(128) NOT NULL,
    loginpassword VARCHAR(128) NOT NULL,
    profilephone VARCHAR(16),
    mapsphone VARCHAR(16),
    added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    done TIMESTAMP DEFAULT NULL,
    coderequest TIMESTAMP DEFAULT NULL,
    confirmed TIMESTAMP DEFAULT NULL,
    PRIMARY KEY(profileId),
    INDEX corpnameIndex (corpname),
    INDEX corpserialIndex (corpserial)
)Engine=InnoDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

'comments' -table 

CREATE TABLE IF NOT EXISTS comments(
    commentId INT NOT NULL AUTO_INCREMENT,
    comment VARCHAR(1024) NOT NULL,
    commentdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    profileId INT NOT NULL,
    PRIMARY KEY(commentId),
    CONSTRAINT fk_ProfilesProfileId FOREIGN KEY(profileId) REFERENCES profiles.profileId ON UPDATE RESTRICT ON DELETE CASCADE
)Engine=InnoDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

It is possible that I am dumb, and I'm just missing something. Hopefully it's so.

Improve this question
2
  • This is side note: done TIMESTAMP DEFAULT NULL, and this works? no need DEFAULT keyword, just set NULL. and also, why use TIMESTAMP type and NULL together ? Commented Mar 9, 2015 at 8:38
  • @OTARIKI If done is not null, then the profile has been done. If it's done, I need to get the date when it was done. That's my reasoning for TIMESTAMP and NULL. I am aware that TIMESTAMP DEFAULT NULL isn't required. I just like to have clean SQL-queries for clarification in documents that are important (in this case, my graduation work). Commented Mar 9, 2015 at 9:18

1 Answer 1

Reset to default
2

I replicated your example locally. It seems that the problem is how you declare your constraint for the foreign key.

CREATE TABLE profiles (
  profileId int(11) NOT NULL AUTO_INCREMENT,
  corpname varchar(512) NOT NULL DEFAULT 'VIRHE',
  corpserial varchar(16) NOT NULL DEFAULT 'VIRHE',
  loginusername varchar(128) NOT NULL,
  loginpassword varchar(128) NOT NULL,
  profilephone varchar(16) DEFAULT NULL,
  mapsphone varchar(16) DEFAULT NULL,
  added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  done timestamp NULL DEFAULT NULL,
  coderequest timestamp NULL DEFAULT NULL,
  confirmed timestamp NULL DEFAULT NULL,
  PRIMARY KEY (profileId),
  KEY corpnameIndex (corpname(255)),
  KEY corpserialIndex (corpserial)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS comments(
    commentId INT NOT NULL AUTO_INCREMENT,
    comment VARCHAR(1024) NOT NULL,
    commentdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    profileId INT NOT NULL,
    PRIMARY KEY(commentId),
    CONSTRAINT fk_ProfilesProfileId FOREIGN KEY(profileId) REFERENCES profiles(profileId) ON UPDATE RESTRICT ON DELETE CASCADE
)Engine=InnoDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

with respect to your code:

  1. The syntax for the foreign key after REFERENCES is table(field) rather than table.field
  2. TIMESTAMP DEFAULT NULL should be TIMESTAMP NULL.
  3. Note the partial index corpnameIndex, some considerations follow hereunder.

Some information about indexes the engine you want to use is InnoDB, which specifies that (from here):

By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.

The problem in your table is INDEX corpnameIndex (corpname), because this index would be 512*3 = 1536 bytes long.

You have some options here:

  1. Change corpname size to 255 by declaring it as follows corpname VARCHAR(255) NOT NULL DEFAULT 'VIRHE'

  2. Enable innodb_large_prefix on your mysql by running set global innodb_large_prefix = ON;. This will bring the limit to 3072 bytes (1024 chars).

  3. Index only a part of your corpname field by using ADD INDEX (corpname(255))
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

That's good information, and extremely useful to know. I'd upvote you just for this info (if I had 15rep). However, this does not fix my problem. The problem is in the creation of comments table. profiles table is already up and running, and there's also valid data there which is in use.
The error appears during the creation of the comments table. That means I cannot use ALTER TABLE because it doesn't exist.
I simulated the whole thing locally. The error you denote lies in the definition of the foreign key. See the updated answer.
That fixed it! It was a syntax error. I could swear I've used REFERENCES table.column in CREATE TABLE and FOREIGN KEY before. I feel really, really dumb right now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.