为了将字段定义为
foreign key，引用的父字段必须在其上定义了索引。
根据有关
foreign key约束的文档：
参考parent_tbl_name（index_col_name，…）
定义
INDEX上
workouts.workoutName，
paymentFor.paymentName和
supplements.supplementName分别。并确保子列定义必须与其父列定义匹配。
更改
workouts表定义如下：
CREATE TABLE workouts( workoutId int(100) NOT NULL AUTO_INCREMENT, workoutName VARCHAr(100) NOT NULL, description VARCHAr(7500) NOT NULL, duration VARCHAr(30), KEY ( workoutName ), -- <---- this is newly added index key ConSTRAINT PRIMARY KEY(workoutId, workoutName));
更改
supplements表定义如下：
CREATE TABLE supplements( supplementId int(100) NOT NULL AUTO_INCREMENT, supplementName VARCHAr(250) NOT NULL, manufacture VARCHAr(100), description VARCHAr(150), qtyonHand INT(5), unitPrice DECIMAL(11,2), manufactureDate DATE, expirationDate DATE, KEY ( supplementName ), -- <---- this is newly added index key ConSTRAINT PRIMARY KEY(supplementId, supplementName));
更改
paymentFor表定义如下：
CREATE TABLE paymentFor( payId int(100) NOT NULL AUTO_INCREMENT, payName VARCHAr(100) NOT NULL, amount DECIMAL(11,2), KEY ( payName ), -- <---- this is newly added index key ConSTRAINT PRIMARY KEY(payId, payName));
现在，如下更改子表定义：
CREATE TABLE sales( saleId int(100) NOT NULL AUTO_INCREMENT, accountNo int(100) NOT NULL, payName VARCHAr(100) NOT NULL, nextPayment DATE, supplementName VARCHAr(250) NOT NULL, qty int(11), workoutName VARCHAr(100) NOT NULL, sDate datetime NOT NULL DEFAULT NOW(), totalAmount DECIMAL(11,2) NOT NULL, ConSTRAINT PRIMARY KEY(saleId, accountNo, payName), ConSTRAINT FOREIGN KEY(accountNo) REFERENCES accounts(accountNo) ON DELETE CASCADE ON UPDATE CASCADE, ConSTRAINT FOREIGN KEY(payName) REFERENCES paymentFor(payName) ON DELETE CASCADE ON UPDATE CASCADE, ConSTRAINT FOREIGN KEY(supplementName) REFERENCES supplements(supplementName) ON DELETE CASCADE ON UPDATE CASCADE, ConSTRAINT FOREIGN KEY(workoutName) REFERENCES workouts(workoutName) ON DELETE CASCADE ON UPDATE CASCADE);
参考 ：
- MySQL使用FOREIGN KEY约束
[ConSTRAINT [符号]]外键
[index_name]（index_col_name，…）
参考tbl_name（index_col_name，…）
[ON DELETE reference_option]
[ON UPDATE reference_option]reference_option：
RESTRICT | 级联| SET NULL | 不采取行动
