MySQL外键导致表丢弃 [英] MySQL foreign key causing table to drop
问题描述
有一个问题之前,这个代码已解决,并导致表工作,但现在我收到错误1005,并且该表不能创建由于fk
这里是第一个表的代码
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE如果存在客户;
客户$ var $(255),
客户$ var $(255),$ b $客户$ b $(
CustomerNumber int NOT NULL,
CustomerPhoneNumber varchar(255) ,
PayPalDate varchar(255),
PayPalDate varchar(255),
Private Key(CustomerNumber)
);
这里是第二个表的代码
DROP TABLE IF EXISTS Policies;
CREATE TABLE策略
(
PolicyID int NOT NULL,
PolicyNumber int NOT NULL,
PetType varchar(255),
CustomerNumber int NOT NULL,
PetName varchar(255),
EffectiveDate varchar(255),
PRIMARY KEY(PolicyID),
CONSTRAINT fk_CustomerNumber
FOREIGN KEY(CustomerNumber)
REFERENCES Customer (CustomerNumber),
CONSTRAINT fk_PetName
FOREIGN KEY(PetName)
REFERENCES Customer(PetName)
);
错误代码是
19:05:38CREATE TABLE策略(PolicyID int NOT NULL,PolicyNumber int NOT NULL,PetType varchar(255),CustomerNumber int NOT NULL,PetName varchar(255),EffectiveDate varchar(255),PRIMARY KEY PolicyID),CONSTRAINT fk_CustomerNumber FOREIGN KEY(CustomerNumber)REFERENCES Customer(CustomerNumber),CONSTRAINT fk_PetName FOREIGN KEY(PetName)REFERENCES Customer(PetName))错误代码:1005.无法创建表'cis22723684.Policies'(errno:150)0.031 sec
我已经解决了编码问题。
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE如果存在客户;
客户$ var $(255),
客户$ var $(255),$ b $客户$ b $(
CustomerNumber int NOT NULL,
CustomerPhoneNumber varchar(255) ,
PayPalDateValchar(255),
PendingPayDate varchar(255),
PendingPayDate varchar(255),
PeeraryKey(CustomerNumber, PetName)
);
DROP TABLE IF EXISTS政策;
CREATE TABLE策略
(
PolicyID int NOT NULL,
PolicyNumber int NOT NULL,
PetType varchar(255),
CustomerNumber int NOT NULL,
PetName varchar(255),
EffectiveDate varchar(255),
PRIMARY KEY(PolicyID),
CONSTRAINT fk_CustomerNumber
FOREIGN KEY(CustomerNumber,PetName)
参考客户(CustomerNumber,PetName)
);
它似乎是使用pk作为复合并且包含一个约束下的pks
然而,当我试图创建一个新的fk我收到了同样的错误代码像以前一样,尽管保持代码使用
第一个表
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE如果存在客户;
客户$ var $(255),
客户$ var $(255),$ b $客户$ b $(
CustomerNumber int NOT NULL,
CustomerPhoneNumber varchar(255) ,
PayPalDateValchar(255),
PendingPayDate varchar(255),
PendingPayDate varchar(255),
PeeraryKey(CustomerNumber, PetName)
);
第二个表
DROP TABLE IF EXISTS Policies;
CREATE TABLE策略
(
PolicyID int NOT NULL,
PolicyNumber int NOT NULL,
PetType varchar(255),
CustomerNumber int NOT NULL,
PetName varchar(255),
EffectiveDate varchar(255),
PRIMARY KEY(PolicyID),
CONSTRAINT fk_CustomerNumber_PetName
FOREIGN KEY(CustomerNumber,PetName)
参考客户(CustomerNumber,PetName)
);
第三个表格
删除表如果存在索赔;
CREATE TABLE声明
(
ClaimsID int NOT NULL,
AmountForReimbursement varchar(255),
PolicyID varchar(255),
PRIMARY KEY(ClaimsID) ,
CONSTRAINT fk_PolicyID
FOREIGN KEY(PolicyID)
REFERENCES策略(PolicyID)
);
是一个简单的事情,int NOT NULL被识别为varchar,我需要一些sleep:')
在第二个约束中,您试图引用非关键列 PetName
这是不可能的,这是违反标准化的概念。您的客户
表只定义主键为 PRIMARY KEY(客户编号)
。
CONSTRAINT fk_PetName
FOREIGN KEY(PetName)
REFERENCES Customer(PetName)
从你的 CREATE
语句中删除上面提到的FK约束定义,它工作得很好。不要相信然后看到这个小提琴: http://sqlfiddle.com/#!9/86a927
根据您的编辑:您的FK约束定义必须如下所示。看到修改小提琴: http://sqlfiddle.com/#!9/3a019 (CustomerNumber,PetName)
FOREIGN KEY(CustomerNumber,PetName)
REFERENCES Customer(CustomerNumber,PetName)
CONSTRAINT fk_CustomerNumber_PetName
had an issue before with this code which was resolved and caused the table to work but now i am receiving error 1005 and the table cannot be created due to the fk
here is the code for the first table
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS Customer;
CREATE TABLE Customer
(
CustomerNumber int NOT NULL,
CustomerName varchar(255),
CustomerAddress varchar(255),
CustomerPhoneNumber varchar(255),
JoinDate varchar(255),
PetName varchar(255),
PayScheme varchar(255),
PremiumPayDate varchar(255),
PRIMARY KEY (CustomerNumber)
);
and here is the code for the second table
DROP TABLE IF EXISTS Policies;
CREATE TABLE Policies
(
PolicyID int NOT NULL,
PolicyNumber int NOT NULL,
PetType varchar(255),
CustomerNumber int NOT NULL,
PetName varchar(255),
EffectiveDate varchar(255),
PRIMARY KEY (PolicyID),
CONSTRAINT fk_CustomerNumber
FOREIGN KEY (CustomerNumber)
REFERENCES Customer(CustomerNumber),
CONSTRAINT fk_PetName
FOREIGN KEY (PetName)
REFERENCES Customer(PetName)
);
error code is
19:05:38CREATE TABLE Policies ( PolicyID int NOT NULL, PolicyNumber int NOT NULL, PetType varchar(255), CustomerNumber int NOT NULL, PetName varchar(255), EffectiveDate varchar(255), PRIMARY KEY (PolicyID), CONSTRAINT fk_CustomerNumber FOREIGN KEY (CustomerNumber) REFERENCES Customer(CustomerNumber), CONSTRAINT fk_PetName FOREIGN KEY (PetName) REFERENCES Customer(PetName) ) Error Code: 1005. Can't create table 'cis22723684.Policies' (errno: 150) 0.031 sec
i have resolved the issue see code edited.
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS Customer;
CREATE TABLE Customer
(
CustomerNumber int NOT NULL,
CustomerName varchar(255),
CustomerAddress varchar(255),
CustomerPhoneNumber varchar(255),
JoinDate varchar(255),
PetName varchar(255),
PayScheme varchar(255),
PremiumPayDate varchar(255),
PRIMARY KEY (CustomerNumber, PetName)
);
DROP TABLE IF EXISTS Policies;
CREATE TABLE Policies
(
PolicyID int NOT NULL,
PolicyNumber int NOT NULL,
PetType varchar(255),
CustomerNumber int NOT NULL,
PetName varchar(255),
EffectiveDate varchar(255),
PRIMARY KEY (PolicyID),
CONSTRAINT fk_CustomerNumber
FOREIGN KEY (CustomerNumber, PetName)
REFERENCES Customer(CustomerNumber, PetName)
);
it appeared to be using the pk as composite and including the pks under one constraint
however when i attempt to create a new fk i am receiving the same error code as before despite keeping the code used
first table
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS Customer;
CREATE TABLE Customer
(
CustomerNumber int NOT NULL,
CustomerName varchar(255),
CustomerAddress varchar(255),
CustomerPhoneNumber varchar(255),
JoinDate varchar(255),
PetName varchar(255),
PayScheme varchar(255),
PremiumPayDate varchar(255),
PRIMARY KEY (CustomerNumber, PetName)
);
second table
DROP TABLE IF EXISTS Policies;
CREATE TABLE Policies
(
PolicyID int NOT NULL,
PolicyNumber int NOT NULL,
PetType varchar(255),
CustomerNumber int NOT NULL,
PetName varchar(255),
EffectiveDate varchar(255),
PRIMARY KEY (PolicyID),
CONSTRAINT fk_CustomerNumber_PetName
FOREIGN KEY (CustomerNumber, PetName)
REFERENCES Customer(CustomerNumber, PetName)
);
third table
DROP TABLE IF EXISTS Claims;
CREATE TABLE Claims
(
ClaimsID int NOT NULL,
AmountForReimbursement varchar(255),
PolicyID varchar(255),
PRIMARY KEY (ClaimsID),
CONSTRAINT fk_PolicyID
FOREIGN KEY (PolicyID)
REFERENCES Policies(PolicyID)
);
was a simple matter of the int NOT NULL being identified as varchar, i need some sleep :')
Problem is in the second constraint as below where you are trying to reference a non-key column PetName
which is not possible and it's against normalization concept. Your Customer
table defines only primary key as PRIMARY KEY (CustomerNumber)
.
CONSTRAINT fk_PetName
FOREIGN KEY (PetName)
REFERENCES Customer(PetName)
Remove the above mentioned FK constraint definition from your CREATE
statement and it works just fine. Don't believe then see this fiddle: http://sqlfiddle.com/#!9/86a927
Per your edit: Your FK constraint definition has to be like below. See the modified fiddle: http://sqlfiddle.com/#!9/3a019
CONSTRAINT fk_CustomerNumber_PetName
FOREIGN KEY (CustomerNumber, PetName)
REFERENCES Customer(CustomerNumber, PetName)
这篇关于MySQL外键导致表丢弃的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!