关系:students (1 can have N) addresses
场景:学生可以有多个记录,但只有一个关联的记录必须将“current
”字段设置为“Yes
”(其他值为NULL
),因此下面的查询应始终只返回每个学生一条记录。
SELECT * FROM address WHERE student_id = 5 AND current = 'Yes'
问题:
人们有时会在同一个学生的插入或更新后将多个记录标记为“是”,因此我需要避免这样做。在MySQL中使用触发器或存储过程的最佳方法是什么?
如果更新发生在“address”表上,那么应该在某个地方运行,将其他记录标记为空:
UPDATE addresses SET current = NULL WHERE student_id = IN_student_id
如果在“address”表上发生INSERT,那么应该在某个地方运行,将其他记录标记为空:
UPDATE addresses SET current = NULL WHERE student_id = IN_student_id AND id <> IN_inserted_id
提前谢谢
最佳答案
如果需要在修改数据后自动更新某些内容,正确的方法是触发。注意,触发器可能调用存储过程。
但是,您将无法在触发器中实现所描述的行为:
存储的函数或触发器无法修改调用该函数或触发器的语句已在使用(用于读取或写入)的表。
事实上,“Address X is the current Address”信息应该存储在students
表的列中,作为address
表的外键。因此,单一性是有保证的。
像这样的东西(because):
CREATE TABLE student (
id INT NOT NULL PRIMARY KEY,
current_address INT,
name VARCHAR(20)
);
CREATE TABLE address (
id INT NOT NULL PRIMARY KEY,
student_id INT NOT NULL,
contents VARCHAR(50) NOT NULL,
CONSTRAINT student_fk FOREIGN KEY student_fk_idx (student_id)
REFERENCES student(id)
);
ALTER TABLE student
ADD CONSTRAINT curraddr_fk_idx
FOREIGN KEY curraddr_fk_idx (id, current_address)
REFERENCES address(student_id, id);
注意这个结构允许插入没有“当前地址”的
students
。这是因为两个表中至少有一个表的外键必须允许NULL
值(否则我们不能在两个表中添加一行)。如果这更有意义,那么让address.student_id
变成NULL
,并允许address
成为nobody的地址,直到创建相应的student
。关于mysql - UPDATE和INSERT之后在存储过程中更新同一表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/17402762/