使用共享ID空间分隔外键 [英] Separate foreign keys with shared ID space

问题描述

让我们说我有一个类型层次结构:(只是一个例子）


一般实体客户：


创建表customer（customer_id int，customer_name varchar（250），

customer_type int）


三个特定客户子类型：1 - business，2 - home，3 - < br $> b $ b大学


由以下三个表格表示（作为子类型，他们共享

customer_id的ID空间）


CREATE TABLE business_customer（customer_id int，business_sector int，

....）

CREATE TABLE home_customer（customer_id int，household_income_bracket

int，...）

CREATE TABLE university_customer（customer_id int，number_students int，

....）


是否可以制定一个前缀键约束：


busineness_customer表中的customer_id必须仅引用客户表中的那些

customer_id customer_type == 1？

home_customer表中的customer_id必须仅引用customer_type == 2的客户表中的那些
customer_id？

谢谢！


- 罗伯特

解决方案

****通过post.usenet.com上的新闻阅读器免费发布****


嗨罗伯特

是否可以制定一个关键词约束：

busineness_customer中的customer_id表必须仅引用customer_type == 1的customer表中的那些客户ID？

home_customer表中的customer_id必须仅引用customer_type ==的customer表中的那些客户ID 2？

使用外键，您只能引用主键。由于customer_type

不是它的一部分，你不能在子类型上使用它。


另一方面，如果你将customer_type添加到客户''主键，

你也应该在子类型上添加它。但是单独的外键不会足够，即你应该在子类型上添加一个检查约束以确保

这样的规则。


==>没有好（从设计的角度来看）外键强制执行

这样的约束。

克里斯


- = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - =

*** Usenet.com - 地球上排名第一的Usenet新闻组服务！ ***
http://www.usenet.com

无限下载 - 19个单独的服务器 - 90,000个组 - 未经审查

- = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - =

ro ************* @ yahoo.com （罗伯特布朗）在留言新闻中写道：< 24 ************************** @ posting.google。 com> ...

让我们说我有一个类型层次结构:(只是一个例子）

一般实体客户：

CREATE TABLE客户（customer_id int，customer_name varchar（250），
customer_type int）

三个特定的客户子类型：1 - business，2 - home，3 -
university customer_id的ID空间）

CREATE TABLE business_customer（customer_id int，business_sector int，
。 ..）
CREATE TABLE home_customer（customer_id int，household_income_bracket
int，...）
CREATE TABLE university_customer（customer_id int，number_students int，
...）

是否可以制定一个foreing key约束条件：



busineness_customer表中的customer_id必须仅引用customer_type == 1的客户表中的那些客户ID？ br />
home_customer表中的customer_id必须是r客户表中只有那些客户表= customer_type == 2？

谢谢！

- 罗伯特

类似于：


CREATE TABLE客户（

customer_id int not null主键，

customer_name varchar（250 ）not null，

customer_type int not null check（customer_type in（1,2,3）），

unique（customer_id，customer_type）

）;


CREATE TABLE business_customer（
customer_id int not null主键，

customer_type int not null default 1 check（ customer_type = 1），

...

外键（customer_id，customer_type）

引用客户（customer_id，customer_type）

）;


等


HTH

/ Lennart

看起来类型层次结构是一种常见的技术关系

设计。在这样的情况下，人们使用什么方法来提供关系完整性

？我应该将customer_type列添加到

子类型，然后使用复合外键吗？我想这可能会起作用

但是它有一个缺点就是使用表空间和一个

customer_type列，它将始终具有相同的子类型值。

只考虑在这种情况下开展业务的成本吗？

Christian Antognini < CH ***************** @ trivadis.com>在留言新闻中写道：< 41 ******** @ post.usenet.com> ...

****通过post.usenet.com上的新闻阅读器免费发布****

嗨罗伯特

是否可以制定一个关键的约束条件：

busineness_customer中的customer_id表必须仅引用customer_type == 1的customer表中的那些客户ID？

home_customer表中的customer_id必须仅引用customer_type ==的customer表中的那些客户ID 2？

使用外键，您只能引用主键。由于customer_type
不属于它，因此您无法在子类型上使用它。

另一方面，如果您将customer_type添加到客户的主键，
你应该在子类型上添加它。但是单独使用外键就不够了，即你应该在子类型上添加一个检查约束来确保这样的规则。

==>没有好的（从设计的角度来看）外键强制执行这样的约束。

克里斯

- = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - =
*** Usenet.com - 地球上排名第一的Usenet新闻组服务！ ***
http://www.usenet.com
无限下载 - 19个单独的服务器 - 90,000个组 - 未经审查
- = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - =

Let''s say I have a type hierarchy: (just an example)

the general entity customer:

CREATE TABLE customer(customer_id int, customer_name varchar(250),
customer_type int)

three specific customer subtypes: 1 - business, 2 - home, 3 -
university

represented by the following three tables (being subtypes, they share
ID space for customer_id)

CREATE TABLE business_customer(customer_id int, business_sector int,
.... )
CREATE TABLE home_customer(customer_id int, household_income_bracket
int, ...)
CREATE TABLE university_customer(customer_id int, number_students int,
....)

Is it possible to make a foreing key constraint that says:

the customer_id in busineness_customer table must reference only those
customer_id in the customer table where customer_type == 1?

the customer_id in home_customer table must reference only those
customer_id in the customer table where customer_type == 2?
Thanks!

- Robert

解决方案

**** Post for FREE via your newsreader at post.usenet.com ****

Hi Robert

Is it possible to make a foreing key constraint that says:

the customer_id in busineness_customer table must reference only those
customer_id in the customer table where customer_type == 1?

the customer_id in home_customer table must reference only those
customer_id in the customer table where customer_type == 2?

With a foreign key you can only reference a primary key. Since customer_type
is not part of it, you cannot use it on the subtypes.

On the other side, if you add customer_type to the customer''s primary key,
you should add it on the subtype as well. But the foreign key alone will not
be enough, i.e. you should add a check constraint on the subtype to ensure
such a rule.

==> There is no good (from a design point of view) foreign key to enforce
such a constraint.
Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

ro*************@yahoo.com (Robert Brown) wrote in message news:<24**************************@posting.google. com>...

Let''s say I have a type hierarchy: (just an example)

the general entity customer:

CREATE TABLE customer(customer_id int, customer_name varchar(250),
customer_type int)

three specific customer subtypes: 1 - business, 2 - home, 3 -
university

represented by the following three tables (being subtypes, they share
ID space for customer_id)

CREATE TABLE business_customer(customer_id int, business_sector int,
... )
CREATE TABLE home_customer(customer_id int, household_income_bracket
int, ...)
CREATE TABLE university_customer(customer_id int, number_students int,
...)

Is it possible to make a foreing key constraint that says:

the customer_id in busineness_customer table must reference only those
customer_id in the customer table where customer_type == 1?

the customer_id in home_customer table must reference only those
customer_id in the customer table where customer_type == 2?
Thanks!

- Robert

Something like:

CREATE TABLE customer (
customer_id int not null primary key,
customer_name varchar(250) not null,
customer_type int not null check (customer_type in (1,2,3)),
unique (customer_id, customer_type )
);

CREATE TABLE business_customer (
customer_id int not null primary key,
customer_type int not null default 1 check (customer_type = 1),
...
foreign key (customer_id, customer_type)
references customer (customer_id, customer_type)
);

etc.

HTH
/Lennart

It seems like type hierarchies are a common technique in relation
design. What approaches do people use to provide relational integrity
in cases like this? Should I add the customer_type column to the
subtypes and then use composite foreign key? I guess that could work
but it has the downside of using up tablespace with a column of
customer_type that will always have the same value for the subtype. Is
that considered just the cost of doing business in this situation?
"Christian Antognini" <ch*****************@trivadis.com> wrote in message news:<41********@post.usenet.com>...

**** Post for FREE via your newsreader at post.usenet.com ****

Hi Robert

Is it possible to make a foreing key constraint that says:

the customer_id in busineness_customer table must reference only those
customer_id in the customer table where customer_type == 1?

the customer_id in home_customer table must reference only those
customer_id in the customer table where customer_type == 2?

With a foreign key you can only reference a primary key. Since customer_type
is not part of it, you cannot use it on the subtypes.

On the other side, if you add customer_type to the customer''s primary key,
you should add it on the subtype as well. But the foreign key alone will not
be enough, i.e. you should add a check constraint on the subtype to ensure
such a rule.

==> There is no good (from a design point of view) foreign key to enforce
such a constraint.
Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

这篇关于使用共享ID空间分隔外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！