How do i add foreign keys to two Innob tables so that they autoupdate each other?

1
votes

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:

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

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

    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 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 ;

1
mysqldatabase-designforeign-keysinnodb

1 Answers

3
votes

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.

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.

Re: your followup question in the comment:

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.

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

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