Fail to create relationship between two tables

Question

I have two tables :

CREATE TABLE `Users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL DEFAULT '',
  `last_name` varchar(50) NOT NULL DEFAULT '',
  `login` varchar(50) NOT NULL DEFAULT '',
  `password` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

AND

CREATE TABLE `Books` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL DEFAULT '',
  `author` varchar(50) NOT NULL DEFAULT '',
  `year` int(4) NOT NULL,
  `available` int(3) NOT NULL DEFAULT '0',
  `availabledate` date NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

I am trying to create a relationship between those two, so that one user may have multiple books (user_id) but whatever I'm doing I'm getting errors. Either

Cannot add or update a child row: a foreign key constraint fails (bookstore., CONSTRAINT books_fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE)

or before I didn't use unsigned int in the Books table and I said that default value is 0 (which I would prefere but I don't think I can do that?) In that case I got error 150.

fancyPants · Accepted Answer · 2014-01-28T13:25:06.060

I'd recommend, to change your database schema. Why?

Can a book exist without having a user? If yes, you shouldn't have a foreign key from books referencing users. Can a user exist without having a book? If yes, you shouldn't have a foreign key from users referencing books.
Can a user have multiple books? And a book multiple users? If yes, you have a m:n relationship. This means you need a bridge table.

In your tables you don't need foreign keys:

CREATE TABLE `Users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL DEFAULT '',
  `last_name` varchar(50) NOT NULL DEFAULT '',
  `login` varchar(50) NOT NULL DEFAULT '',
  `password` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

CREATE TABLE `Books` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL DEFAULT '',
  `author` varchar(50) NOT NULL DEFAULT '',
  `year` int(4) NOT NULL,
  `available` int(3) NOT NULL DEFAULT '0',
  `availabledate` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

And a bridge table would look like this:

CREATE TABLE books_users (
book_id int(11) unsigned NOT NULL,
user_id int(11) unsigned NOT NULL,
PRIMARY KEY (book_id, user_id),
KEY idx_user_id (user_id),
FOREIGN KEY fk_books (book_id) REFERENCES Books(id),
FOREIGN KEY fk_users (user_id) REFERENCES Users(user_id)
) ENGINE=InnoDB;

This solves both problems and is common practice.

To query both users and books in one query, you join them like this:

SELECT
whatever
FROM
Books b
INNER JOIN books_users bu ON b.id = bu.book_id
INNER JOIN users u ON bu.user_id = u.user_id
WHERE user_id = 1 /*for example*/
;

If you want to insert something in the tables, just do the insert and get the id which was generated for the row with SELECT LAST_INSERT_ID();, then insert this id in the books_users bridge table.

Updates don't affect anything, you can simply perform them on users or books. If you really really have to update the auto_increment column (which usually isn't needed and not recommended) you can add ON UPDATE CASCADE after the foreign keys in the books_users table.

@Fengson Please consider my answer, although Chintan Gor has more upvotes. He solves the problem, that you can't add a foreign key to the table, but your table design might still be wrong. — fancyPants, Jan 28 '14 at 13:14
I created a tables as you said, because of the fact that I want to have both books and users alone, too. But can you tell me how can I save and gather informations this way? When I place an order, what do I update? And when I want to display asd well? — Fengson, Jan 28 '14 at 13:17

score 0 · Answer 2 · answered Jan 28 '14 at 12:37

Change these lines and try again

change user_id int(11) unsigned NOT NULL AUTO_INCREMENT to user_id int(11) NOT NULL AUTO_INCREMENT

and

id int(11) unsigned NOT NULL AUTO_INCREMENT, to id int(11) NOT NULL AUTO_INCREMENT,

and

user_id int(11) unsigned NOT NULL, to user_id int(11) NOT NULL,

Finally try

CREATE TABLE `Users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL DEFAULT '',
  `last_name` varchar(50) NOT NULL DEFAULT '',
  `login` varchar(50) NOT NULL DEFAULT '',
  `password` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

and

CREATE TABLE `Books` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL DEFAULT '',
  `author` varchar(50) NOT NULL DEFAULT '',
  `year` int(4) NOT NULL,
  `available` int(3) NOT NULL DEFAULT '0',
  `availabledate` date NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Chintan Gor · Answer 3 · 2014-01-28T12:42:14.187

-2

If you are using Mysql then you must use InnoDB database engine to enable relationship between two tables MyISAM will not allow this relationship

alter table `users` add constraint `FK_users` FOREIGN KEY (`user_id`) REFERENCES `books` (`user_id`) ON DELETE NO ACTION  ON UPDATE NO ACTION

or you can use this query for cascade edit delete

alter table `users` add constraint `FK_users` FOREIGN KEY (`user_id`) REFERENCES `books` (`user_id`) ON DELETE CASCADE  ON UPDATE CASCADE

This is your new DDL for two tables try this

CREATE TABLE IF NOT EXISTS `books` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL DEFAULT '',
  `author` varchar(50) NOT NULL DEFAULT '',
  `year` int(4) NOT NULL,
  `available` int(3) NOT NULL DEFAULT '0',
  `availabledate` date NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL DEFAULT '',
  `last_name` varchar(50) NOT NULL DEFAULT '',
  `login` varchar(50) NOT NULL DEFAULT '',
  `password` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `FK_users` FOREIGN KEY (`user_id`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

edited Jan 28 '14 at 12:42

answered Jan 28 '14 at 12:20

Chintan Gor

1,062
2
15
35

let me check sir your defination – Chintan Gor Jan 28 '14 at 12:32
please check I added new query to add relation for edit and delete – Chintan Gor Jan 28 '14 at 12:34
1

Surely MyISAM's not bothered? – Strawberry Jan 28 '14 at 13:29
@Strawberry yes sir please see this link http://stackoverflow.com/questions/12971246/why-doesnt-mysqls-myisam-engine-support-foreign-keys – Chintan Gor Jan 30 '14 at 06:48
1

Precisely. MyISAM simply ignores the constraint. The relationship is not disallowed! – Strawberry Jan 30 '14 at 08:02
@Strawberry you mean I can use Relation query in MyIsam and it will not giving any error? although relation will not consider by Mysql.. – Chintan Gor Jan 30 '14 at 11:17
1

Exactly. It's just ignored. :-) – Strawberry Jan 30 '14 at 11:19
ThankYou sir I did not tried this query in MYIsam engine because from first people thought me that it is not allowed in this engine so I never tried this ....Thanks Again :D – Chintan Gor Jan 30 '14 at 11:21

Fail to create relationship between two tables

3 Answers3