CODEWITHSUNDEEP
mysqlsqlindexingmaxlength
Mia
Mia

Reputation: 579

SQL error: 1170

I am getting the following error when running the below code...what is wrong with it?

Error Code:1170. BLOB/TEXT column 'message" used in key specification without a key length.

-- MySQL Script generated by MySQL Workbench
-- Sun Jun  4 21:25:42 2017
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

    SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
    SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
    SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
    CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
    USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `mydb`.`users` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `first_name` VARCHAR(255) NOT NULL,
      `last_name` VARCHAR(255) NOT NULL,
      `email` VARCHAR(255) NOT NULL,
      `password` VARCHAR(255) NOT NULL,
      `created_at` DATETIME NOT NULL,
      `updates_at` DATETIME NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE INDEX `first_name_UNIQUE` (`first_name` ASC),
      UNIQUE INDEX `last_name_UNIQUE` (`last_name` ASC),
      UNIQUE INDEX `email_UNIQUE` (`email` ASC),
      UNIQUE INDEX `password_UNIQUE` (`password` ASC),
      UNIQUE INDEX `created_at_UNIQUE` (`created_at` ASC),
      UNIQUE INDEX `updates_at_UNIQUE` (`updates_at` ASC))
      ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`messages`
-- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `mydb`.`messages` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `users_id` INT NOT NULL,
      `message` TEXT NOT NULL,
      `created_at` DATETIME NOT NULL,
      `updated_at` DATETIME NOT NULL,
      PRIMARY KEY (`id`),
      INDEX `fk_messages_users_idx` (`users_id` ASC),
      UNIQUE INDEX `id_UNIQUE` (`id` ASC),
      UNIQUE INDEX `users_id_UNIQUE` (`users_id` ASC),
      UNIQUE INDEX `message_UNIQUE` (`message` ASC),
      UNIQUE INDEX `created_at_UNIQUE` (`created_at` ASC),
      UNIQUE INDEX `updated_at_UNIQUE` (`updated_at` ASC),
      CONSTRAINT `fk_messages_users`
        FOREIGN KEY (`users_id`)
        REFERENCES `mydb`.`users` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
      ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`comments`
-- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `mydb`.`comments` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `users_id` INT NOT NULL,
      `messages_id` INT NOT NULL,
      `comment` TEXT NOT NULL,
      `created_at` DATETIME NOT NULL,
      `updated_at` DATETIME NOT NULL,
      PRIMARY KEY (`id`),
      INDEX `fk_comments_users1_idx` (`users_id` ASC),
      INDEX `fk_comments_messages1_idx` (`messages_id` ASC),
      UNIQUE INDEX `id_UNIQUE` (`id` ASC),
      UNIQUE INDEX `users_id_UNIQUE` (`users_id` ASC),
      UNIQUE INDEX `messages_id_UNIQUE` (`messages_id` ASC),
      UNIQUE INDEX `comment_UNIQUE` (`comment` ASC),
      UNIQUE INDEX `created_at_UNIQUE` (`created_at` ASC),
      UNIQUE INDEX `updated_at_UNIQUE` (`updated_at` ASC),
      CONSTRAINT `fk_comments_users1`
        FOREIGN KEY (`users_id`)
        REFERENCES `mydb`.`users` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_comments_messages1`
        FOREIGN KEY (`messages_id`)
        REFERENCES `mydb`.`messages` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;


    SET SQL_MODE=@OLD_SQL_MODE;
    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Upvotes: 2

Views: 4025

Answers (2)

Rick James
Rick James

Reputation: 142316

It is rare for a table to have 6 unique keys. Even 2 is rare; 1 (the PRIMARY KEY) is typical.

UNIQUE is two things... an index and a uniqueness constraint. As Bill points out, a "prefix" index can achieve a form of indexing. Uniqueness is more complex. If you really need that, then we can discuss it.

A uniqueness constraint on a DATETIME or TIMESTAMP is risky because there (usually) could be two events happening in the same second. It is also rare to need a uniqueness check on "continuous" values like that. (Ditto for FLOAT.)

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562508

InnoDB index maximum length is 767 bytes by default.

When you declare your unique index on your TEXT column, which has a maximum length of 65535 bytes, it exceeds the maximum index length by a lot.

UNIQUE INDEX `message_UNIQUE` (`message` ASC),

You can enable innodb_large_prefix which makes it possible to make an index of 3072 bytes, that's also much too small for a complete TEXT column.

You can us a prefix index for a TEXT column, but that's not a good solution to enforce uniqueness.

INDEX `message` (`message`(255) ASC),

You should reconsider whether you need a unique constraint on a TEXT column.

See also:

Upvotes: 1

Related Questions