是否有可能使用“NOT IN”创建外键约束？逻辑 [英] Is it possible to create a foreign key constraint using "NOT IN" logic

问题描述

是否可以在一个表上添加一个外键约束，这将允许在另一个表中不存在的值？

在下面的例子中，两个表都包含字段USER_ID。约束条件是客户和员工不能具有相同的USER_ID值。

在添加新表格或以任何方式更改其中一个表格方面，我非常有限。

 客户
 --------------------- ----- 
 USER_ID varchar2（10）
 
 EMPLOYEE 
 ----------------------- --- 
 USER_ID varchar2（10）
  

我想到了一些解决方法，例如一个视图，其中包含来自两个表的数据或在表中添加一个插入触发器，我可以修改。

解决方案

事情是存在的，尽管它可能是伪造的。

如果你想这样做关系（这将比视图/触发器好很多），简单的选择是添加所有员工ID和一个 C 到所有客户ID。然而，如果你有其他的属性，并且你想确保它们不是同一个人（即你不只是对这个ID感兴趣），那么这将不起作用。

<如果是这种情况，您需要创建第三个表，我们称之为人员：

  create table people（
 user_id varchar2（10）not null 
，user_type varchar2（1）not null 
，constraint pk_people主键（user_id）
，约束chk_people_user_types check（user_type in（'C'，' E'））
）; 
  

C 代表客户， E 用于检查约束中的员工。然后您需要在PEOPLE上创建一个唯一的索引/约束：

 在user（user_id，user_type）上创建索引ui_people_id_type; 
  

就我个人而言，我会停在这里，完全放弃您的CUSTOMER和EMPLOYEE表。他们不再有任何用处，你的问题已经解决了。

如果你没有能力添加新的列/表，你需要和谁做和说服他们改变它。过度复杂的事情只会导致逻辑和混乱的错误（相信我 - 使用视图意味着你需要大量的触发器来维护你的表，并且你必须确保有人只更新视图）。然而，如果你真的想要继续下去，那么做好事情会更容易，即使花费更长时间也是如此。改变你的CUSTOMER和EMPLOYEE表来包含他们的USER_TYPE，并确保它对于表中的每一行都是一样的，即：

$ p $ alter table customers add user_type default'C'not null;
alter table customers add constraint chk_customers_type
check（user_type不为null，user_type ='C'）;


Is it possible to add a foreign key constraint on a table which will allow values which do NOT exist in another table?

In the example below, both tables contain the field USER_ID. The constraint is that a customer and and an employee cannot have the same USER_ID value.

I am very limited in adding new tables or altering one of the tables in any way.

CUSTOMER
--------------------------
USER_ID     varchar2(10)

EMPLOYEE
--------------------------
USER_ID     varchar2(10)

I thought of a few workarounds, such as a view which contains the data from both tables or adding an insert trigger on the table I can modify.

解决方案

No, no such thing exists, though it is possible to fake.

If you want to do this relationally (which would be a lot better than views/triggers) the easy option is to add a E to all employee IDs and a C to all customer IDs. However, this won't work if you have other attributes and you want to ensure they're not the same person (i.e. you're not just interested in the ID).

If this is the case you need to create a third table, let's call it PEOPLE:

create table people ( 
   user_id varchar2(10) not null
 , user_type varchar2(1) not null
 , constraint pk_people primary key (user_id)
 , constraint chk_people_user_types check ( user_type in ('C','E') )
   );

C would stand for customer and E for employee in the check constraint. You then need to create a unique index/constraint on PEOPLE:

create index ui_people_id_type on people ( user_id, user_type );

Personally, I'd stop here and completely drop your CUSTOMER and EMPLOYEE tables; they no longer have any use and your problem has been solved.

If you don't have the ability to add new columns/tables you need to speak to the people who do and convince them to change it. Over-complicating things only leads to errors in logic and confusion (believe me - using a view means you need a lot of triggers to maintain your tables and you'll have to ensure that someone only ever updates the view). It's a lot easier to do things properly, even if they take longer.

---

However, if you really want to continue you alter your CUSTOMER and EMPLOYEE tables to include their USER_TYPE and ensure that it's always the same for every row in the table, i.e.:

alter table customers add user_type default 'C' not null;
alter table customers add constraint chk_customers_type
      check ( user_type is not null and user_type = 'C' );

这篇关于是否有可能使用“NOT IN”创建外键约束？逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！