如何对外键引用字段设置数据库完整性检查

本文介绍了如何对外键引用字段设置数据库完整性检查的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有四个数据库表，如下所示：任何 BookExtension 通过外键记录间接指向两个不同的 ID_Company 字段：可以在Sql Server BookExtension 中的任何新记录必须包含 Book.ID_Company = Discount.ID_Company ？ 简而言之，我想让以下Query必须返回0记录！ SELECT count来自BookExtension INNER JOIN预订在BookExstension.ID_Book = Book.ID_Book INNER JOIN折扣On BookExstension.ID_Discount = Discount.ID_Discount WHERE Book.ID_Company<> Discount.ID_Company 或以纯英文： 我不想a BookExtension 记录引用公司的书记录和折扣另一个不同的公司的记录！ 解决方案除非我误解了你的意图，否则你使用的SQL语句的一般形式是 ALTER TABLE FooExtension ADD CONSTRAINT your-constraint-name CHECK（ID_Foo = ID_Bar）; 假定现有数据已经​​符合新约束。如果现有数据不符合，您可以修复数据（假设它需要修复），或者也可以通过检查ID_FooExtension的值来限制新约束的范围（可能）。 （假设您可以通过ID_FooExtension的值来标识新行。） 稍后。 。 谢谢，我确实误解了你的情况。 据我所知，您不能在SQL Server中以您希望的方式强制使用该约束，因为它不允许CHECK约束中的SELECT查询。 （我可能在SQL Server 2008中的错误。）一个常见的解决方法是在一个函数中包装一个SELECT查询，并调用该函数，但是根据我学到的是不可靠的。 $ b $ $ b （ID_Book，ID_Company）。它的一部分将类似于 UNIQUE（ID_Book，ID_Company）。 在折扣（ID_Discount，ID_Company）上创建UNIQUE约束。 / li> 将两列添加到 BookExtension - Book_ID_Company和 Discount_ID_Company。 填充这些新列。 在BookExtension中更改外键约束。你想要 BookExtension（ID_Book， Book_ID_Company）来引用 Book（ID_Book，ID_Company）。类似的外键更改 引用折扣。 一个检查约束以保证BookExtension.Book_ID_Company与BookExtension.Discount_ID_Company相同。 I have four Database Tables like these:Any BookExtension record via foreign keys points indirectly to two different ID_Company fields:Is it possible to enforce in Sql Server that any new record in BookExtension must have Book.ID_Company = Discount.ID_Company ?In a nutshell I want that the following Query must return 0 record!SELECT count(*) from BookExtension INNER JOIN Book ON BookExstension.ID_Book = Book.ID_BookINNER JOIN Discount ON BookExstension.ID_Discount = Discount.ID_DiscountWHERE Book.ID_Company <> Discount.ID_Companyor, in plain English:I don't want that a BookExtension record references a Book record of a Company and a Discount record of another different Company! 解决方案 Unless I've misunderstood your intent, the general form of the SQL statement you'd use isALTER TABLE FooExtensionADD CONSTRAINT your-constraint-nameCHECK (ID_Foo = ID_Bar);That assumes existing data already conforms to the new constraint. If existing data doesn't conform, you can either fix the data (assuming it needs fixing), or you can limit the scope (probably) of the new constraint by also checking the value of ID_FooExtension. (Assuming you can identify "new" rows by the value of ID_FooExtension.)Later . . .Thanks, I did indeed misunderstand your situation.As far as I know, you can't enforce that constraint the way you want to in SQL Server, because it doesn't allow SELECT queries within a CHECK constraint. (I might be wrong about that in SQL Server 2008.) A common workaround is to wrap a SELECT query in a function, and call the function, but that's not reliable according to what I've learned.You can do this, though.Create a UNIQUE constraint on Book(ID_Book, ID_Company). Part of it will look like UNIQUE (ID_Book, ID_Company).Create a UNIQUE constraint on Discount (ID_Discount, ID_Company).Add two columns toBookExtension--Book_ID_Company andDiscount_ID_Company.Populate those new columns.Change the foreign key constraintsin BookExtension. You wantBookExtension (ID_Book,Book_ID_Company) to referenceBook (ID_Book, ID_Company). Similar change for the foreign keyreferencing Discount.Now you can add a check constraint to guarantee that BookExtension.Book_ID_Company is the same as BookExtension.Discount_ID_Company. 这篇关于如何对外键引用字段设置数据库完整性检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！