mysql与外键相反

Question

我有2个表，客户和附属公司。我需要确保customers.email和affiliates.email是独占的。换句话说，一个人不能既是客户又是附属公司。它基本上是外键的对立面。有没有办法做到这一点？

Answer 1

没有键可以做到这一点，但听起来你不应该使用两个表。相反，您可以有一个表包含customer/affiliate数据(在此表中必须是唯一的)和另一个包含类型(customer/affiliate)的表。

CREATE TABLE People (
   pplid,
   pplEmail,
   ptid,
   UNIQUE KEY (pplEmail)
)
CREATE TABLE PeopleType (
   ptid,
   ptType
)
INSERT INTO PeopleType VALUES (1, 'affiliates'), (2, 'customers');

Answer 2

您可以尝试以下操作。创建一个新表，它将成为客户和附属公司的主目录：

CREATE TABLE party
( 
  id int not null auto_increment primary key ,
  party_type enum('customer','affiliate') not null,
  email varchar(100),
  UNIQUE (id,party_type)
);
--Then
CREATE TABLE customer
(
   ....
   party_id INT NOT NULL,
   party_type enum('customer') NOT NULL DEFAULT 'customer',
   PRIMARY KEY (party_id,party_type)
   FOREIGN KEY (party_id,party_type) REFERENCES party(id,party_type)
);
CREATE TABLE affiliates
(
   ....
   party_id INT NOT NULL,
   party_type enum('affiliate') NOT NULL DEFAULT 'affiliate',
   PRIMARY KEY (party_id,party_type)
   FOREIGN KEY (party_id,party_type) REFERENCES party(id,party_type)
)
-- enum is used because mysql still doesn't have CHECK constraints  

这样，每一方只能是一种类型。