CODEWITHSUNDEEP
phpmysqlmysqli
Filip
Filip

Reputation: 41

Foreign key constraint makes SELECT query return 0 rows

I've been given this database structure:

DROP TABLE IF EXISTS `account`;
CREATE TABLE IF NOT EXISTS `account` (
  `accountType` varchar(120) NOT NULL,
  PRIMARY KEY (`accountType`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `username`;
CREATE TABLE IF NOT EXISTS `username` (
  `usernameID` int(11) NOT NULL AUTO_INCREMENT,
  `password` varchar(200) NOT NULL,
  `name` varchar(200) NOT NULL,
  `phoneNr` int(15) NOT NULL,
  `email` varchar(120) NOT NULL,
  `accountType` varchar(120) NOT NULL,
  PRIMARY KEY (`usernameID`),
  KEY `accountType` (`accountType`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `username`
  ADD CONSTRAINT `username_ibfk_1` FOREIGN KEY (`accountType`) REFERENCES `account` (`accountType`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

Now, I want to SELECT a user from the username table. In MySQL workbench I get the results I'm looking for with:

SELECT name FROM username;

However, when using a mysqli query in PHP i get num_rows => 0 in my result object, like this:

(The var_dump above the result object is the query I'm sending in)

It works if I remove the KEY from the username table, so I assume that's where the problem lies. How should I alter my SELECT query?

Upvotes: 0

Views: 119

Answers (2)

Filip
Filip

Reputation: 41

For some reason it seems like InnoDB was the issue. As soon as I removed ENGINE=InnoDB from the username table, everything worked.

Upvotes: 0

Chris
Chris

Reputation: 4810

I believe name is a reserved word in MySQL. You may just need to escape it using the backtick character like so,

SELECT `name` FROM username;

See Keywords and Reserved Words.

Upvotes: 0

Related Questions