对delete set null使用多个外键的Mysql create table
对delete set null使用多个外键的Mysql create table
提问于 2013-04-08 21:15:53
我正在尝试创建一个带有多个外键和delete/ update约束的数据库，但我得到一个错误代码1005，其中包含以下sql脚本：
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;错误代码显示无法创建葡萄园表，我只想知道使用delete/update控件创建多个外键的正确格式。
回答 4
Stack Overflow用户
回答已采纳
发布于 2013-04-08 21:26:28
您的外键规则是ON DELETE SET NULL，但是您的列定义是NOT NULL。
要么更改列定义并删除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;SQLFiddle demo
Stack Overflow用户
发布于 2014-08-07 03:35:17
尝试不带外键的create table(innoDB enginer)，并使用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;参考：
- http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
技巧:不建议在行为与所使用的操作系统不同的表名称中使用大写字母(或驼峰大小写)：
- http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html
Stack Overflow用户
发布于 2016-09-29 21:50:09
访问：
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
);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/15880070
复制相关文章
相似问题
腾讯云开发者
