如何将外键添加到两个Innob表中，以便它们相互自动更新？

I have two tables users and lead_contacts which have similar data. When somebody buys a product they become a user. How should I modify the two create staements below so:

我有两个表用户和lead_contacts,它们具有相似的数据。当有人购买产品时,他们就会成为用户。我应该如何修改下面的两个创建标记:

that the leads table receives a new entry with first_name, last_name, company and email, when a user is created.

创建用户时,潜在客户表会收到带有first_name,last_name,company和email的新条目。

the first_name, last_name, company and email in users table is updated automatically when the leads table info is changed

当潜在客户表信息发生更改时,用户表中的first_name,last_name,company和email将自动更新

CREATE TABLE lead_contacts (
contact_id int(11) NOT NULL auto_increment,
user_id int(11) unsigned NOT NULL default '0',
email varchar(100) NOT NULL default '',
company varchar(50) NOT NULL default '',
first_name varchar(50) NOT NULL default '',
last_name varchar(50) NOT NULL default '',
address varchar(100) NOT NULL default '',
address_2 varchar(100) NOT NULL default '',
city varchar(50) NOT NULL default '',
state varchar(50) NOT NULL default '',
country varchar(50) NOT NULL default '',
postal_code varchar(30) NOT NULL default '',
phone varchar(30) NOT NULL default '',
fax varchar(30) NOT NULL default '',
ship_bill_same enum('Y', 'N') NOT NULL default 'Y',
notes text NOT NULL,
admin_notes text NOT NULL,
list_name varchar(50) NOT NULL default '',
lead_list int(11) unsigned NOT NULL default '0',
is_master_list enum('N', 'Y') NOT NULL default 'N',
active_work enum('Y', 'N') NOT NULL default 'Y',
parentID int(11) unsigned NOT NULL default '0',
PRIMARY KEY (contact_id),
KEY user_id (user_id),
KEY lead_list (lead_list),
KEY is_master_list (is_master_list),
KEY active_work (active_work),
KEY parentID (parentID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1;

CREATE TABLE lead_contacts(contact_id int(11)NOT NULL auto_increment,user_id int(11)unsigned NOT NULL default'0',email varchar(100)NOT NULL default'',company varchar(50)NOT NULL default'',first_name varchar (50)NOT NULL default'',last_name varchar(50)NOT NULL default'',address varchar(100)NOT NULL default'',address_2 varchar(100)NOT NULL default'',city varchar(50)NOT NULL default '',state varchar(50)NOT NULL default'',country varchar(50)NOT NULL default'',postal_code varchar(30)NOT NULL default'',phone varchar(30)NOT NULL default'',fax varchar( 30)NOT NULL默认'',ship_bill_same枚举('Y','N')NOT NULL默认'Y',注释文本NOT NULL,admin_notes文本NOT NULL,list_name varchar(50)NOT NULL默认'',lead_list int( 11)unsigned NOT NULL默认'0',is_master_list枚举('N','Y')NOT NULL默认'N',active_work枚举('Y','N')NOT NULL默认'Y',parentID int(11 )unsigned NOT NULL默认值'0',PRIMARY KEY(contact_id),KEY user_id(user_id ),KEY lead_list(lead_list),KEY is_master_list(is_master_list),KEY active_work(active_work),KEY parentID(parentID))ENGINE = InnoDB DEFAULT CHARSET = latin1 PACK_KEYS = 1;

CREATE TABLE users (
userID int(11) NOT NULL auto_increment,
access_level int(11) NOT NULL default '0',
username varchar(100) NOT NULL default '',
password varchar(100) NOT NULL default '',
first_name varchar(50) NOT NULL default '',
last_name varchar(50) NOT NULL default '',
company varchar(100) NOT NULL default '',
email varchar(100) NOT NULL default '',
PRIMARY KEY (userID),
UNIQUE KEY username (username)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

CREATE TABLE用户(userID int(11)NOT NULL auto_increment,access_level int(11)NOT NULL默认'0',用户名varchar(100)NOT NULL默认'',密码varchar(100)NOT NULL默认'',first_name varchar( 50)NOT NULL default'',last_name varchar(50)NOT NULL default'',company varchar(100)NOT NULL default'',email varchar(100)NOT NULL default'',PRIMARY KEY(userID),UNIQUE KEY username (用户名))ENGINE = InnoDB DEFAULT CHARSET = latin1;

1 个解决方案

#1

I think you misunderstand how foreign keys work.

我想你误解了外键是如何工作的。

A reference from leads to users means that a row must already exist in users before a row in leads can reference it.

从潜在客户到用户的引用意味着在潜在客户中的行可以引用它之前,用户必须已存在行。

There's no way in SQL to make a dependent table automatically create a row in its parent table on demand.

SQL中没有办法让依赖表根据需要在其父表中自动创建一行。

You could do this with a trigger, I suppose. But not a foreign key constraints. Besides, the values to fill into the parent table must come from somewhere. Either you need to specify them in an INSERT statement in your application or in a trigger, or else use the defaults defined for each column in the users table. Given that you have a unique constraint on users.username, I don't think this would be possible from a trigger.

我猜你可以用触发器做到这一点。但不是外键限制。此外,填充到父表中的值必须来自某个地方。您需要在应用程序或触发器的INSERT语句中指定它们,或者使用为users表中的每个列定义的默认值。鉴于您对users.username有一个唯一约束,我认为这不可能来自触发器。

Re: your followup question in the comment:

Re:您在评论中的后续问题:

No, a foreign key can't do what you're describing. When you modify info in the leads table (the table with the foreign key), the only thing a foreign key can do is prevent the modification if you try to change the leads.user_id column to a value that is not found in the users table.

不,外键不能做你所描述的。修改引导表(具有外键的表)中的信息时,如果您尝试将leads.user_id列更改为在users表中找不到的值,则外键唯一可以执行的操作是阻止修改。

The foreign key in the child (leads) table can't change anything in the parent (users) table.

子(lead)表中的外键不能更改父(用户)表中的任何内容。

I'm not sure what is the source of your mistaken understanding. Did you read it somewhere or see someone do something like this?

我不确定你错误理解的来源是什么。你在某个地方看过它还是看到有人做过这样的事情?

#1