Search code examples
mysqlsqlcheck-constraintsmysql-json

Sql constraint error references other column

I don't know a lot of mysql and have an error in my sql script. Currently running mysql 8.0.24. Does anyone know what might be the problem?

Error: https://prnt.sc/226xk5x

Sql:

-- Dumping structure for table gtav_rp2._vehicle


CREATE TABLE IF NOT EXISTS `_vehicle` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `cid` int(11) unsigned NOT NULL,
      `vin` varchar(50) NOT NULL DEFAULT '',
      `type` varchar(50) NOT NULL DEFAULT '',
      `size` int(11) NOT NULL,
      `plate` varchar(50) NOT NULL DEFAULT '',
      `model` varchar(50) NOT NULL DEFAULT '',
      `name` varchar(50) DEFAULT NULL,
      `garage` varchar(59) DEFAULT NULL,
      `state` varchar(50) DEFAULT NULL,
      `appearance` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid('appearance')),
      `mods` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid('mods')),
      `data` longtext DEFAULT NULL,
      `damage` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid('damage')),
      `degredation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid('degredation')),
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- Dumping data for table gtav_rp2._vehicle: ~0 rows (approximately)
    /*!40000 ALTER TABLE `_vehicle` DISABLE KEYS */;
    /*!40000 ALTER TABLE `_vehicle` ENABLE KEYS */;
    
    /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
    /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
    /*!40101 SET CHARACTER

_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
Solution

  • Your CHECK constraint does not reference the column. Or any column, actually. By using single-quotes, you're using a string literal, not a column name.

      `appearance` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL 
    CHECK (json_valid('appearance')),

    I assume this is meant to be:

      `appearance` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL 
    CHECK (json_valid(`appearance`)),

    Use the right type of quotes for SQL identifiers, not string literals.

    In addition, this CHECK constraint would be unnecessary if you used the JSON data type instead of LONGTEXT. The JSON data type already enforces that the content of the column must be valid JSON format.

    MySQL's JSON data type already uses utf8mb4 character set and utf8mb4_bin collation.

    So your column definition could be simply as follows:

      `appearance` JSON