CODEWITHSUNDEEP
mysqlphpmyadmin
Ashraf Kamarudin
Ashraf Kamarudin

Reputation: 542

Creating foreign key between 2 tables

Ok, so it's like this. I have 2 tables in phpmyadmin. One is for personal details and the other is for login information. Both tables have AccountID, so I tried using foreign key constraints to connect the tables. After I did that it seems like I cannot update the table with new data. Before the constraint, updating the tables worked fine.

What I'm trying to do is store user login info and personal info in these table. Then whenever the user wants to delete their current account, the personal details and login details of are deleted from both tables or when they wanted to search for their login and personal info the search engine can search from both tables with AccountID.

so far.i have make 2 new tables.1 table which is personal information have 'AccountID'[A_I][PRIMARY] and 'loginID'.another table is login info.it has 'loginID'[A_i][PRIMARY]

i already make the 'loginID' at personal info and index but i cannot assign foreign key constraint for it bcause it did not detect 'loginID' in personal info.

Upvotes: 0

Views: 726

Answers (1)

Sagar Panda
Sagar Panda

Reputation: 561

Your AccountId should be a primary key in one table say Personal Info table. This AccountId should be the foreign key in another table (Login) and make sure you set on Delete Cascade and on Update Cascade. So in this structure, when personal info is deleted, its corresponding record in the login table will be automatically deleted.

DROP TABLE IF EXISTS `stacktest`.`personal_info`;
CREATE TABLE  `stacktest`.`personal_info` (
  `account_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `age` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `stacktest`.`login_info`;
CREATE TABLE  `stacktest`.`login_info` (
  `loginId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`loginId`),
  KEY `FK_login_info_1` (`account_id`),
  CONSTRAINT `FK_login_info_1` FOREIGN KEY (`account_id`) REFERENCES `personal_info` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

Above is the way how the 2 tables should be created. Then insert some values in both table,

Note that account_id value has to be same in both the tables.

After that you can fire a delete query like:

delete from personal_info where accound_id=2;

This will delete rows from parent table personal_info and also from child table login_info where account_id is 2

Keeping the account_id as NOT NULL in child table:

DROP TABLE IF EXISTS `stacktest`.`login_info`;
CREATE TABLE  `stacktest`.`login_info` (
  `loginId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`loginId`),
  KEY `FK_login_info_1` (`account_id`),
  CONSTRAINT `FK_login_info_1` FOREIGN KEY (`account_id`) REFERENCES `personal_info` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

Upvotes: 1

Related Questions