引用 mysql_在MYSQL中使用引用

REFERENCES关键字是

foreign key constraint的一部分,它使MySQL要求引用表的指定列中的值也存在于引用表的指定列中.

这可以防止外键引用不存在或被删除的ID,并且可以选择阻止您在仍然引用行时删除它们.

一个具体的例子是,如果每个员工都必须属于某个部门,那么您可以从employee.departmentid引用department.id添加外键约束.

运行以下代码以创建两个测试表tablea和tableb,其中tableb中的列a_id引用tablea的主键. tablea填充了几行.

CREATE TABLE tablea (

id INT PRIMARY KEY,

foo VARCHAR(100) NOT NULL

) Engine = InnoDB;

INSERT INTO tablea (id, foo) VALUES

(1, 'foo1'),

(2, 'foo2'),

(3, 'foo3');

CREATE TABLE tableb (

id INT PRIMARY KEY,

a_id INT NOT NULL,

bar VARCHAR(100) NOT NULL,

FOREIGN KEY fk_b_a_id (a_id) REFERENCES tablea (id)

) Engine = InnoDB;

现在尝试以下命令：

INSERT INTO tableb (id, a_id, bar) VALUES (1, 2, 'bar1');

-- succeeds because there is a row in tablea with id 2

INSERT INTO tableb (id, a_id, bar) VALUES (2, 4, 'bar2');

-- fails because there is not a row in tablea with id 4

DELETE FROM tablea WHERE id = 1;

-- succeeds because there is no row in tableb which references this row

DELETE FROM tablea WHERE id = 2;

-- fails because there is a row in tableb which references this row

重要说明：两个表都必须是InnoDB表,否则将忽略约束.