Mysql – How to do data entry with Foreign Key integer IDs

foreign keyMySQL

This seems like a basic question to me but I'm having trouble finding practical solutions.

I have two tables, one for video game consoles, and another for the games themselves.

Consoles
+ -- + --------- +
| ID | Name      |
+ -- + --------- +
| 0  | Console X |
+ -- + --------- +
| 1  | Console Y |
+ -- + --------- +

Games
+ -- + ------ + ------- +
| ID | Name   | Console |
+ -- + ------ + ------- +
| 0  | Game X | ?       |
+ -- + ------ + ------- +
| 1  | Game Y | ?       |
+ -- + ------ + ------- +
| 2  | Game Z | ?       |
+ -- + ------ + ------- +

My question is how do I do the data entry from scratch for the Game.Console column here? As a human, it'd be easy to write the Console.Name there, but obviously that's not recommended to use as a Foreign Key ID. But if I use the integer Console.ID as the Foreign Key, for 700 games and 30 consoles, it seems like I'd have to manually cross-check the table as I enter in all those numbers.

How do I do the task of data entry for an association like this? Is there a better solution than manually entering numbers? I'd think if I was using an IDE there might be some feature for a drop-down of foreign rows to fill in a foreign key value, but I haven't found anything.

Additional Info: I'm not expecting user input in this application, just my own personal data, so I'm not worried about validation, just how to alleviate tedium in the initial data entry. Entering cross-referenced integers seems like the job for a computer or tool.

Best Answer

I think the easiest solution for you would be to create a stored procedure that took @GameName and @ConsoleName.

CREATE PROCEDURE InsertGame @GameName VARCHAR(50), @ConsoleName VARCHAR(50)
AS
BEGIN
DECLARE @ConsoleID INT

SELECT @ConsoleID = ConsoleID FROM Console WHERE ConsoleName = @ConsoleName

IF @ConsoleID IS NULL
BEGIN
INSERT Console (Name) SELECT @ConsoleName
SELECT @ConsoleID = scope_identity()
END

INSERT Game (Name, Console) SELECT @GameName, @ConsoleID

END

If you entered in a @ConsoleName that didn't already exist in the Console table, it would INSERT it there. Next the stored procedure would look up the ID for the console name you provided it, and INSERT a record into the Game table with @GameName and the @ConsoleID.

Then you'd call that stored procedure for each Game you were planning to enter:

EXEC InsertGame 'Grand Theft Auto 5', 'PS4'
EXEC InsertGame 'Grand Theft Auto 5', 'PC'
EXEC InsertGame 'Final Fantasy XV', 'PS4'
EXEC InsertGame 'Overwatch', 'PC'
EXEC InsertGame 'Civilization 5', 'PC'

And so on until you've inserted all of your games.

Please note that the exact syntax I've used is for the database platform I am most familiar with, but the underlying logic should be usable in any database platform.

Also, as I write this I am struck by the idea that what you really want is to support a many-to-many relationship between game titles and platforms, since a platform can have many games and a game can be available on multiple platforms. That would take a little more work, but would be helpful for efficient storage and would give you the ability to easily determine which platform(s) a given game was available for without doing a text-match.

So, at a high level you'd need another table. The existing Game table would not have a ConsoleID column. Instead there would be a new table, likely called Console_Game, that would include only ConsoleID and GameID as keys. Then the stored procedure would need to check for both the existence of ConsoleName, and the existence of GameName. Inserting into both tables as necessary, and then finally write a record to Console_Game to show that a given game is available on a given console.

This will be more work upfront, but it is a more scalable solution than what you currently have. If this is for a job and not a hobby you should probably go that route.

Related Solutions

MySQL: update instead of delete if foreign key constraint

You can't directly do it since there's no way (that I can think of) to alternately UPDATE or DELETE from the same query... generally a query can only do one type of CRUD operation (ON DUPLICATE KEY UPDATE being an exception to this).

You could, however, make it "feel like" a single query, with a stored procedure, where you'd set up a CONTINUE HANDLER for the foreign key error you anticipate. The handler will trap the error and can be used to set a variable which you can test to see whether you hit the error and therefore need to also try the update query.

DELIMITER $$

DROP PROCEDURE IF EXISTS delete_or_update $$
CREATE PROCEDURE delete_or_update (IN input_value INT)
BEGIN

  DECLARE hit_fk_error TINYINT DEFAULT 0;
  DECLARE deleted_row_count INT DEFAULT NULL;
  DECLARE updated_row_count INT DEFAULT NULL;

  -- 1451 is probably the error code that will be generated
  -- Cannot delete or update a parent row: a foreign key constraint fails (%s)
  -- otherwise, you may need to substitute the correct error code in the next line

  DECLARE CONTINUE HANDLER FOR 1451 SET hit_fk_error = TRUE;

  DELETE FROM parent_table WHERE tested_column = input_value; /* arg to the stored proc */
  SET deleted_row_count = ROW_COUNT();

  IF (hit_fk_error = TRUE) THEN
    UPDATE parent_table SET some_other_value = 'something_else'
     WHERE tested_column = input_value;
    SET updated_row_count = ROW_COUNT();
  END If;

  SELECT deleted_row_count, updated_row_count, hit_fk_error;

END $$

DELIMITER ;

This will try the delete; if it hits a foreign key error it will then try the update (and the foreign key error will be suppressed). The procedure will return a record set with the number of rows affected by each query and whether the foreign key error occurred. If the "input_value" isn't an INT or the WHERE clause is more complex, you'll need to modify the structure, accordingly.

The potential problem I see here is that if the where clause is too broad, and matches some rows that would cause a foreign key error and other rows that wouldn't, then the query will not treat the rows individually. That could be accomplished by a more sophisticated procedure that used a cursor to find the rows in the where clause and tried to delete or update them individually by primary key after identifying them. That approach would be less efficient but more precise.

Mysql – Foreign key for rows added later

I would suggest the following:

Allow null foreign keys in the main table.
Insert rows minus their foreign keys in the import.
Add a table ImportRelationships that stores relationships during importing.
Rectify the foreign keys in the main table at the end of the import based on ImportRelationships.
Delete the rows from ImportRelationships.

Best Answer

Related Solutions

MySQL: update instead of delete if foreign key constraint

Mysql – Foreign key for rows added later

Related Question