Relational data bases: problem when entering data


I'm trying to create a database where two tables have a relation to an intermediate table.

The idea is simple; a table stores user records, their passwords, emails etc, while the other stores levels. (The levels belong to a kind of videogame where the registered user can select which level he wants to play, and in the intermediary table a query is made when he has passed it.) niveles stores attributes such as the level, the description of the level and the points obtained after passing the level.

So we have the table usuarios , the table niveles and the table niveles_pasados .

niveles_pasados stores only the user's information, the past level, and the points earned by passing it.

What I intend is, that each time the user manages to pass a level, perform a insert into within niveles_pasados , storing the information of the user who passed it, the level it passed, and the points it earned.

I tried to create the tables and relate them to the next dumbbell, but when I entered data in the table niveles_pasados , I returned the following error.

asked by Omar 02.04.2018 в 21:48

1 answer


1) To perform an INSERT in levels_spaces you must first make sure that the records referenced in the table users and in the table levels exist.

That is:

INSERT INTO usuarios VALUES(0, "usuario1","password1","[email protected]");
INSERT INTO niveles VALUES(1, "Nivel 1", 100);
INSERT INTO niveles_pasados VALUES(0, "usuario1",1, 105);

In this way the record in "levels_passed" refers to the user "user1" and level 1

If the record "user1" does not exist, the foreign key is not fulfilled. If the record "Level 1" does not exist, the key is not fulfilled either.

2) If each user has "errors" then why not add one more field to the "users" table?

3) The query that allows you to know the sum of points of the user "user1"

SELECT SUM(Puntos) FROM usuarios u, niveles_pasados n WHERE u.USER = n.Usuario AND u.USER = "usuario1"
answered by 02.04.2018 в 23:23