How to manage one to one relationship in mySql?

Question

Hi all I've two tables one about Customers and the second one is about their Accounts as 'Customer_Account_Information'. we know that one customer can have only one account, so I'm trying to enforce one to one relationship, but i don't know the procedure/syntax in mySql or mySqlyog. is there any one who can help me?

Answer 1

You should create a Foreign Key contraint on table Accounts to table Customers using CustomerID.

Have a look at FOREIGN KEY Constraints

Also then make this a Unique Column in table Accounts

Have a look at MySQL foreign keys - how to enforce one-to-one across tables?

You could also use the same CustomerID from table Customers as PRIMARY KEY column in table Accounts as PRIMARY KEY.

Answer 2

A simple implementation

This is not a perfect solution but it is easy to understand with a little bit of experience with mysql and referential integrity.

You need a foreign key in customers to refer to customer_account_inforamtion. Since the account_id in customers is the only way to join the tables, no customer can have more than one record in customer_account_information.

In the sample definitions below, I've included a constraint which causes the customer to be deleted when their account information is deleted. You may or may not want/need this.

CREATE TABLE customer_account_information (
    id int(11) NOT NULL auto_increment,
    some_attribute varchar(20) NOT NULL,
    another_attribute varchar(30) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE customers (
    id int(11) NOT NULL auto_increment,
    account_id int(11) NOT NULL,
    firstname varchar(30) NOT NULL,
    surname varchar(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX account_id (account_id),
    CONSTRAINT account_id FOREIGN KEY (account_id) REFERENCES customer_account_information (id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The account_id in customers is the foreign key which refers to a record in customer_account_information

Alternative implementation

It might be an idea to revisit the design of the two tables. If each customer really can have only one account then perhaps all of the attributes of each can be merged into one table. Yes this business rule may change in the future but it would be a better idea to defer this work until really necessary - don't over engineer the solution today.