时间戳

首页 > 解决方案 > errno: 121 "Duplicate key on write or update" 当使用 CONSTRAINT 创建表时

mysql - errno: 121 "Duplicate key on write or update" 当使用 CONSTRAINT 创建表时

问题描述

我使用以下代码创建了一个表

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;

第 87 行的错误 1005 (HY000)：无法创建表beersafe。Notes （错误号：121“写入或更新时重复键”）

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 |

标签： mysqlmariadb

解决方案

我使用以下代码创建表“Notes”

CREATE TABLE Notes( idint(11) NOT NULL AUTO_INCREMENT, createdtimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, titletext, contenttext, publictinyint(1) DEFAULT NULL, useridint(11) NOT NULL, beeridint(11) NOT NULL, PRIMARY KEY ( id), KEY userid( userid), KEY beerid( beerid) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

删除了“约束外Notes_ibfk_1键（userid）引用Users（id），约束外Notes_ibfk_2键（beerid）引用Beers（id）”

推荐阅读