mysql - 我该如何修复这个语法以使其正常工作？

Question

我一直在尝试在表上创建外键来引用价格表，这是我到目前为止所使用的语法，这会产生错误，

CREATE TABLE shirts(
        shirt_id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        shirt_name VARCHAR(100) NOT NULL,
        shirt_type VARCHAR(10) NOT NULL,
        shirt_size VARCHAR(20) NOT NULL,
        qp_price NUMERIC(6,2) FOREIGN KEY REFERENCES price_list.price ON price_list.price_id=shirts.qp_price NOT NULL,
        o_price NUMERIC(6,2) FOREIGN KEY REFERENCES price_list.price ON price_list.price_id=shirts.o_price NOT NULL,
        clr_options VARCHAR(30) NULL,
        qty NUMERIC(5,0) NULL
    )ENGINE=INNODB

“价格表”有3列，分别是price_id、price_cat和price。我想要衬衫表中的 qp_price 和 o_price 列做的是根据我放入衬衫表上这些列中的数字显示 Price_list 表的价格列。例如，

如果我愿意

INSERT INTO shirts(shirt_name,shirt_type,shirt_size,qp_price,o_price)VALUES
        ('Crewneck Tee','Men','S','1','2'),
        ('Crewneck Tee','Men','M','1','2'),
        ('Crewneck Tee','Men','L','1','2'),
        ('Crewneck Tee','Men','1X','1','2'),
        ('Crewneck Tee','Men','2X','3','4'),
        ('Crewneck Tee','Men','3X','5','6'),
        ('Crewneck Tee','Men','4X','7','8'),
        ('Crewneck Tee','Men','5X','9','10')

S-L 圆领 T 恤将在价格列的第一行和第二行中显示价格，因为它链接到价格表上的 Price_id 列...我该如何执行此操作？

Answer 1

CREATE TABLE statmenet FOREIGN KEY REFERENCES 子句中的 ON 不是联接，它旨在指定行为，例如 ON UPDATE CASCADE ON DELETE RESTRICT。

此外，我在 14.6.4.4. FOREIGN KEY Constraints 找到了有关 MySQL 5.1 的评论:

Ensure that you indexed both Table 1 and Table 2's referenced columns on Table 1 and Table 2 respectively. If you don't, the error "#1005 - Can't create table 'prospect' (errno:105)" will be flagged. (Indexing is good practice as it avoids full table-scans!) Once this is taken care of and the referenced columns are of same data-type, you would have successfully created your desired table with as many FKs as you want on it.

CREATE TABLE shirts(
    shirt_id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    shirt_name VARCHAR(100) NOT NULL,
    shirt_type VARCHAR(10) NOT NULL,
    shirt_size VARCHAR(20) NOT NULL,
    qp_price NUMERIC(6,2) NOT NULL, 
    o_price NUMERIC(6,2) NOT NULL, 
    clr_options VARCHAR(30) NULL,
    qty NUMERIC(5,0) NULL
    INDEX (qp_price),
    INDEX (o_price),
    FOREIGN KEY (qp_price) REFERENCES price_list(price_id),
    FOREIGN KEY (o_price) REFERENCES price_list(price_id),
)ENGINE=INNODB

仅供引用，http://www.dpriver.com/pp/sqlformat.htm当您碰巧没有特定的数据库环境时，它非常有用。