Reputation: 79
Foreign Keys In MySQL Database
I'm building a simple web-based application for school purposes that should enable certain users to buy certain products they choose from a dropdown menu. Whenever a user clicks to buy a product they chose from a dropdown menu this product gets stored into a purchased items table and at the same time gets removed from another table called products. From what I know, in order to be able to do this I would need to have 3 tables: users, products, purchase and in the purchase table I should have 2 columns with foreign keys that is #1 for the purchaser and #2 for the product purchased. I thought I resolved this issue but my glassfish server keeps throwing the following error at me:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
Cannot add or update a child row: a foreign key constraint fails
(`webprodaja`.`kupljeno`, CONSTRAINT `kupljeno_ibfk_1` FOREIGN KEY (`id`) REFERENCESusers(`id`))
Ok, here are the tables and foreign keys in question:
Table 'users':
id | name
1 | Ivan
2 | Beka
3 | Ogi
Table 'proizvodi' (eng. products):
id | name
1 | kafa
2 | jafa
3 | ratluk
Table 'kupljeno' (eng. purchase):
id | name | proizvod
2 | Beka | 3
3 | Ogi | 2
Table 'kupljeno' also has 2 foreign keys:
One that was put on the 'id' column and references the table 'users' and its column 'id';
Another one that was put on the 'proizvod' column and references the table 'proizvodi' and its column 'id';
So what I'm trying to do is delete whichever product a buyer chose and get its name deleted in the 'proizvod' column and therefore put the same product into the 'kupljeno' column. However, I was able to make a purchase and store the details into 'kupljeno' column but for some reason I can't do this any longer let alone delete the product being bought in the 'proizvod' column.
Also, here's the complete index.jsp code for what it's worth:
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<%
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/webprodaja","root","");
Statement st = conn.createStatement();
// if(request.getParameter("add")!=null){
ResultSet rs = st.executeQuery("select * from users");
%>
<form action="index.jsp" method="post">
<select name="user">
<%
while(rs.next())
{
String name=rs.getString(2);
String id = rs.getString(1);
%>
<option value="<%=name%>" ><%=name%></option>
<%
}
%>
</select>
<%
ResultSet rs1 = st.executeQuery("select * from proizvodi");
%>
<select name="proizvod">
<%
while(rs1.next())
{
String name1=rs1.getString(2);
String id1 = rs1.getString(1);
%>
<option value="<%=id1%>" ><%=name1%></option>
<%
}
%>
</select>
<input type="submit" name="add" value="buy"/>
</form>
<%
if(request.getParameter("add")!=null)
{
String user = request.getParameter("user");
String proizvod = request.getParameter("proizvod");
st.execute("insert into kupljeno(id, name, proizvod) values (null, '"+user+"', "+proizvod+")");
}
%>
<%-- <form action="index.jsp" method="post">
<input type="submit" name="add" value="buy">
</form> --%>
</body>
</html>
Upvotes: 0
Views: 1316
Answers (2)
Reputation: 108450
You really need to provide the value for that foreign key column (rather oddly named id, rather than users_id, in the kupljeno table. And we usually add a primary key on every table, which this one does not seem to have.
insert into kupljeno(id, name, proizvod) values (null, '"+user+"', "+proizvod+")"
^^ ^^^^
I suspect that you have declared the id column with the AUTO_INCREMENT attribute in the kupljeno table, as if it were the primary key of the table, rather than a foreign key to another table.
The reason that I suspect that, because you are inserting a NULL value, but the row that is attempting to be inserted has a value. That's either being assigned because of an AUTO_INCREMENT attribute on the column, or (less likely in this case) it's being assigned by a trigger.
(What would go a long ways to actually identify the problem would be the ACTUAL TABLE DEFINTIONS, which can easily be obtained using a SHOW CREATE TABLE statement.)
What I think you really want is to separate the PRIMARY KEY of the table, from the FOREIGN KEY that references the users table, like this:
id - primary key
user_id - foreign key references user(id)
user_name - (redundant, copied from user.name)
product_id - foreign key to product(id)
Upvotes: 0
Reputation: 2163
you stated,
and at the same time gets removed from another table called products
and also you said,
table 'kupljeno'[purchase] also has 2 foreign keys: one that was put on the 'id' column and references the table 'users' and its column 'id'; another one that was put on the 'proizvod'[product] column and references the table 'proizvodi'[product] and its column 'id';
When a table's row has a fk constraint to another table's row, then the second table's row cannot be deleted until the first table's row is deleted. You should read this article about foreign key constraints.
Why do this? It is bad design. Do not remove from products table.
Instead control this with your code to prevent users from purchasing the same product twice if desired. Do not force MySQL to do what it shouldn't; make your code do what it should!
Upvotes: 2