mysql表与表之间外键非空,无法关联两个MySQL表（外键）

Here is my USER table

CREATE TABLE IF NOT EXISTS `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(100) NOT NULL,

`expiry` varchar(6) NOT NULL,

`contact_id` int(11) NOT NULL,

`email` varchar(255) NOT NULL,

`password` varchar(100) NOT NULL,

`level` int(3) NOT NULL,

`active` tinyint(4) NOT NULL DEFAULT '1',

PRIMARY KEY (`id`,`email`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

And here is my contact_info table

CREATE TABLE IF NOT EXISTS `contact_info` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) NOT NULL,

`email_address` varchar(255) NOT NULL,

`company_name` varchar(255) NOT NULL,

`license_number` varchar(255) NOT NULL,

`phone` varchar(30) NOT NULL,

`fax` varchar(30) NOT NULL,

`mobile` varchar(30) NOT NULL,

`category` varchar(100) NOT NULL,

`country` varchar(20) NOT NULL,

`state` varchar(20) NOT NULL,

`city` varchar(100) NOT NULL,

`postcode` varchar(50) NOT NULL,

PRIMARY KEY (`id`,`email_address`),

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The system uses username to login users. I want to modify it in such a way that it uses email for login. But there is no email_address in users table.

I have added foreign key - email in user table(which is email_address in contact_info).

How should I query database?

解决方案

No, no, no, no no. Seriously, no. Don't make me come over there :-)

You're breaking third normal form by storing the email address twice.

The relationship need only be a short one, that of id. Assuming you're not guaranteeing the IDs will be identical in the two tables (i.e., my users.id isn't necessarily equal to my contact_info.id), just add a ci_id to the users table to act as a foreign key to the contact_info table.

Then the query to get a user's username and email will be something like:

select u.username, ci.email

from users u, contact_info ci

where u.username = 'paxdiablo'

and u.ci_id = ci.id;