Unable to relate two MySQL tables (foreign keys)
- by KPL
Hello people,
Here's 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's 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's 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?
