mysql - 错误号 : 121 "Duplicate key on write or update" when Create Table with CONSTRAINT

Question

我用下面的代码创建了一个表

CREATE TABLE `Notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `title` text,
  `content` text,
  `public` tinyint(1) DEFAULT NULL,
  `userid` int(11) NOT NULL,
  `beerid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userid` (`userid`),
  KEY `beerid` (`beerid`),
  CONSTRAINT `Notes_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `Users` (`id`),
  CONSTRAINT `Notes_ibfk_2` FOREIGN KEY (`beerid`) REFERENCES `Beers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

ERROR 1005 (HY000) at line 87: Can't create table beersafe.Notes (errno: 121 "Duplicate key on write or update")

mysqladmin  Ver 9.1 Distrib 10.0.36-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version      10.0.36-MariaDB-0ubuntu0.16.04.1
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/run/mysqld/mysqld.sock
Uptime:         17 hours 14 min 49 sec

(来自评论)

CREATE TABLE Beers (
    id int(11) NOT NULL AUTO_INCREMENT, 
    name varchar(50) DEFAULT NULL, 
    image text, 
    alcohol float DEFAULT NULL, 
    color varchar(50) DEFAULT NULL, 
    category text, 
    description text, 
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

CREATE TABLE Users (
    id int(11) NOT NULL AUTO_INCREMENT, 
    email text, 
    password text, 
    name text, 
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; 

SELECT  *
    FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE  CONSTRAINT_NAME IN ('Notes_ibfk_1', 'Notes_ibfk_2');

| def | BeerSafe | Notes_ibfk_1 | BeerSafe | Notes | FOREIGN KEY |
| def | BeerSafe | Notes_ibfk_2 | BeerSafe | Notes | FOREIGN KEY |

Answer 1

我用下面的代码创建了表“Notes”

CREATE TABLE `Notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `title` text,
  `content` text,
  `public` tinyint(1) DEFAULT NULL,
  `userid` int(11) NOT NULL,
  `beerid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userid` (`userid`),
  KEY `beerid` (`beerid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

删除:

CONSTRAINT `Notes_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `Users` (`id`),
CONSTRAINT `Notes_ibfk_2` FOREIGN KEY (`beerid`) REFERENCES `Beers` (`id`)