kidantrim21
Programmer
Hi. I'm programming an e-commerce/online store using servlets and a MySQL database, and I'm having some problems with the db right now. Hope somebody here smarter than me can give me a hand.
There are two kinds of users, both having their own table in the db: customers and admins. Both have an ID field, which is their PK.
Then I also have the table 'order', where I store the order ID (PK) and more info about the order, but also the ID of the person who purchased the order, which could be a customer or an admin. So, when I created the table 'order', I put two foreign keys: FK_id_admin (which relates to the field ID in the table 'admin') and FK_id_customer (relates to the field ID in the table 'customer'). Now, when I try to insert a new order purchased by a customer in the database, it won't let me, and will tell me this:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`trigo_musical`.`order`, CONSTRAINT `FK_id_admin` FOREIGN KEY (`id`) REFERENCES `admin` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
And if an admin is the one purchasing the order, it will show the same exception but with the other FK:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`trigo_musical`.`order`, CONSTRAINT `FK_id_customer` FOREIGN KEY (`id`) REFERENCES `customer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
I realize that the problem is that I have two foreign keys in the same table referencing the same field in two different tables. So when I try to add an order purchased by a customer, it will look into the 'customer' table and see that the id is there, OK, but then IT ALSO looks in the 'admin' table and checks, and when it finds nothing that's where the excepcion appears. The problem is that I don't know how to fix this problem with the FK's. What should I change in my database?
Thanks in advance!
There are two kinds of users, both having their own table in the db: customers and admins. Both have an ID field, which is their PK.
Then I also have the table 'order', where I store the order ID (PK) and more info about the order, but also the ID of the person who purchased the order, which could be a customer or an admin. So, when I created the table 'order', I put two foreign keys: FK_id_admin (which relates to the field ID in the table 'admin') and FK_id_customer (relates to the field ID in the table 'customer'). Now, when I try to insert a new order purchased by a customer in the database, it won't let me, and will tell me this:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`trigo_musical`.`order`, CONSTRAINT `FK_id_admin` FOREIGN KEY (`id`) REFERENCES `admin` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
And if an admin is the one purchasing the order, it will show the same exception but with the other FK:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`trigo_musical`.`order`, CONSTRAINT `FK_id_customer` FOREIGN KEY (`id`) REFERENCES `customer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
I realize that the problem is that I have two foreign keys in the same table referencing the same field in two different tables. So when I try to add an order purchased by a customer, it will look into the 'customer' table and see that the id is there, OK, but then IT ALSO looks in the 'admin' table and checks, and when it finds nothing that's where the excepcion appears. The problem is that I don't know how to fix this problem with the FK's. What should I change in my database?
Thanks in advance!
