如何对外键引用字段设置数据库完整性检查 [英] How to set a database integrity check on foreign keys referenced fields

问题描述

我有四个数据库表，如下所示：

预订

ID_Book | ID_Company |说明

BookExtension

ID_BookExtension | ID_Book | ID_Discount

折扣

ID_Discount |说明| ID_Company

公司

ID_Company |描述

任何 BookExtension 通过外键记录间接指向两个不同的 ID_Company 字段：

BookExtension.ID_Book引用包含Book.ID_Company < br>
BookExtension.ID_Discount引用包含Discount.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:

Book
ID_Book |ID_Company|Description

BookExtension
ID_BookExtension | ID_Book| ID_Discount

Discount
ID_Discount | Description | ID_Company

Company
ID_Company | Description

Any BookExtension record via foreign keys points indirectly to two different ID_Company fields:

BookExtension.ID_Book references a Book record that contains a Book.ID_Company
BookExtension.ID_Discount references a Discount record that contains a Discount.ID_Company

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_Book
INNER JOIN Discount ON BookExstension.ID_Discount = Discount.ID_Discount
WHERE Book.ID_Company <> Discount.ID_Company

or, 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 is

ALTER TABLE FooExtension
ADD CONSTRAINT your-constraint-name
CHECK (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.

1. Create a UNIQUE constraint on Book (ID_Book, ID_Company). Part of it will look like UNIQUE (ID_Book, ID_Company).
2. Create a UNIQUE constraint on Discount (ID_Discount, ID_Company).
3. Add two columns to BookExtension--Book_ID_Company and Discount_ID_Company.
4. Populate those new columns.
5. Change the foreign key constraints in BookExtension. You want BookExtension (ID_Book, Book_ID_Company) to reference Book (ID_Book, ID_Company). Similar change for the foreign key
   referencing Discount.

Now you can add a check constraint to guarantee that BookExtension.Book_ID_Company is the same as BookExtension.Discount_ID_Company.

这篇关于如何对外键引用字段设置数据库完整性检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！