MySQL Mysql在删除时创建具有多个外键的表设置为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15880070/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql create table with multiple foreign key on delete set null
提问by Tim Raynor
I am trying to create a database with multiple foreign keys with delete/ update constraints, but I got a error code 1005 with following sql scripts:
我正在尝试使用具有删除/更新约束的多个外键创建一个数据库，但是使用以下 sql 脚本出现错误代码 1005：
CREATE TABLE Worker (
WorkerID smallint auto_increment,
WorkerType varchar(45) NOT NULL,
WorkerName varchar(45) NOT NULL,
Position varchar(45) NOT NULL,
TaxFileNumber int NOT NULL,
Address varchar(100) ,
Phone varchar(20) ,
SupervisorID smallint ,
PRIMARY KEY (WorkerID),
FOREIGN KEY (SupervisorID) REFERENCES Worker(WorkerID)
ON DELETE SET NULL
ON UPDATE CASCADE
)Engine=InnoDB;
CREATE TABLE Grape (
GrapeID smallint NOT NULL,
GrapeType varchar(45) NOT NULL,
JuiceConversionRatio int,
StorageContainer ENUM('Stainless Steel Tank','Oak Barrel'),
AgingRequirement int,
PRIMARY KEY (GrapeID)
)Engine=InnoDB;
CREATE TABLE Vineyard (
VineyardID smallint auto_increment,
VineyardName VARCHAR(45) NOT NULL,
FarmerID smallint NOT NULL,
GrapeID smallint NOT NULL,
ComeFrom varchar(45) NOT NULL,
HarvestedAmount int,
RipenessPercent int,
PRIMARY KEY (VineyardID),
FOREIGN KEY (FarmerID) REFERENCES Worker(WorkerID)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (GrapeID) REFERENCES Grape(GrapeID)
ON DELETE SET NULL
ON UPDATE CASCADE
)Engine=InnoDB;
The error code says that fail to create the Vineyard table, I just want to know the proper format for creating multiple foreign keys with delete/update control.
错误代码表示无法创建 Vineyard 表，我只想知道使用删除/更新控制创建多个外键的正确格式。
回答by juergen d
Your foreign key rule is ON DELETE SET NULLbut your column definition is NOT NULL.
您的外键规则是，ON DELETE SET NULL但您的列定义是NOT NULL.
Either change your column definition and remove the NOT NULLpart or overthink your foreign key rule. That works:
要么更改您的列定义并删除该NOT NULL部分，要么过度考虑您的外键规则。那个有效：
CREATE TABLE Vineyard (
VineyardID smallint auto_increment,
VineyardName VARCHAR(45) NOT NULL,
FarmerID smallint,
GrapeID smallint,
ComeFrom varchar(45) NOT NULL,
HarvestedAmount int,
RipenessPercent int,
PRIMARY KEY (VineyardID),
FOREIGN KEY (FarmerID) REFERENCES Worker(WorkerID)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (GrapeID) REFERENCES Grape(GrapeID)
ON DELETE SET NULL
ON UPDATE CASCADE
)Engine=InnoDB;
回答by fitorec
Try with create table(innoDB enginer) without foreign key and use the update table with constraintsyntax, for example:
尝试使用innoDB enginer没有外键的create table( ) 并使用update table with constraint语法，例如：
ALTER TABLE `vineyard`
ADD CONSTRAINT `relation_farmer_has_many_vineyards`
FOREIGN KEY (`farmer_id`)
REFERENCES `worker` (`worker_id`)
ON DELETE SET NULL
ON UPDATE CASCADE;
Reference:
参考：
Trick:is not recommended to use capital letters(or camel case) in the names of the tables that the behavior differs from the operating system being used:
技巧：不建议在行为与所使用的操作系统不同的表的名称中使用大写字母（或驼峰式大小写）：
回答by Akash
Visit :
访问 ：
https://developer.android.com/training/basics/data-storage/databases.html#DefineContract
https://developer.android.com/training/basics/data-storage/databases.html#DefineContract
Cursor c = db.query(
FeedEntry.TABLE_NAME, // The table to query
projection, // The columns to return
selection, // The columns for the WHERE clause
selectionArgs, // The values for the WHERE clause
null, // don't group the rows
null, // don't filter by row groups
sortOrder // The sort order
);
回答by Nimesh Vagadiya
Visit :
访问 ：
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
CREATE TABLE `ffxi_characterJob` (
`serverID` int(11) NOT NULL,
`userid` int(10)unsigned NOT NULL,
`characterName` varchar(255) NOT NULL,
`jobAbbr` char(4) NOT NULL,
`jobLevel` int(11) default '0',
PRIMARY KEY (`serverID`,`userid`,`characterName`,`jobAbbr`),
INDEX (`jobAbbr`),
CONSTRAINT FOREIGN KEY (`serverID`,`userid`,`characterName`) REFERENCES `ffxi_characters` (`serverID`,`userid`,`characterName`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (`jobAbbr`) REFERENCES `ffxi_jobType` (`jobAbbr`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
