代码之家 › 专栏 › 技术社区 › Tim Abell

如何使外键只能引用目标表中行的子集

foreign-keys sql-server

Tim Abell · 技术社区 · 14 年前

我有一个带有主键的表(我们称之为“person”),还有一个引用它的表(我们称之为“grade”,就像在student grades中一样)。

表“grade”有“field”等级.personid,它是的外键person.personid". 让我们说“人”有“场”人员类型同时(为了简单起见,varchar的值可能是“student”或“teacher”),只有学生才有分数。

我目前正在使用SQLServer2008,但也对其他平台的答案感兴趣。

                 [ grade  ]
[ person ]       [--------]
[--------]       [gradeid ]
[personid] <-FK- [personid]
[type    ]       [data    ]
[name    ]

p、我知道模式绑定视图上的约束,但并不喜欢它们,因为每当有人修改它们所依赖的表时,它们就会中断。

3 回复 | 直到 14 年前

a1ex07 14 年前

对于SQL Server,可以通过定义 INSTEAD OF 触发。例如,对于Mysql服务器,您需要定义 BEFORE 触发。

更新

CREATE TRIGGER ON [grades] INSTEAD INSERT
AS
BEGIN
   IF NOT EXISTS(   
   SELECT 1 FROM [person] WHERE personid = inserted.person_id AND person.type  = 'student'
   )
   BEGIN
      RAISERROR ('Invalid person type', 10, 1);
   END;

   INSERT INTO [grades] SELECT field1, field2, ... FROM inserted;
END
GO

对于mysql,应该是 CREATE TRIGGER ... BEFORE INSERT RAISEERROR ,因此需要生成错误以防止插入。通常,我用 INSERT INTO not_existing_table(id) VALUES(1,2) 获取触发器主体中的运行时错误。

KM. 14 年前

                 [ grade  ]
[ person ]       [--------]
[--------]       [gradeid ]
[personid] <-FK- [personid]
[type    ] <-FK- [type    ]
[name    ]       [data    ]

在personid+type上添加一个FK,并在personid+type上添加一个check约束等级类型只允许学生类型。

Tim Abell 14 年前

为了记录在案,这里有一个匿名版本对我有用:

CREATE TRIGGER trigRestrictGradeToStudents
ON dbo.grade
FOR INSERT, UPDATE
AS
IF NOT EXISTS (
    SELECT person.personid
    FROM person
        INNER JOIN inserted ON person.personid = inserted.personid
    WHERE (type = 'student')        
)
BEGIN
    ROLLBACK TRANSACTION --prevent the insert/update from proceeding
    RAISERROR ('Only students may have grade records', 11, 1)
END

严重性设置为11,使其在错误消息中显示在“事务在触发器中结束”之前。批处理已中止。“

另请参见 RAISERROR