MySQL: Insert a new row at a specific primary key, or alternately, bump all subsequent rows down?

Question

I am creating two tables in a database in MySQL just so I can play around with SQL and learn more, as I am a novice. I have read several questions on Stack relating to inserting a new row, and updating an existing row. My question is a little different, hopefully it won't be considered a dupe as none of the other answers I read gave me the full explanation I need because I think it's the auto-increment part that's confusing me. I don't think I can just go in and assign a new value for the primary keys in one of the tables with auto-increment set up, can I?

I have two tables: english_words and spanish_words. Their primary keys are respectively eng_id and span_id, and are set up to auto-increment. My hope had been to practice SQL and eventually get things set up enough so that I can practice my joins later on. For now, in english_words, I entered a duplicate row by mistake, with the ID 7. I deleted that row, and of course it now goes "6...8..." ..... and when I created my spanish_words table, I forgot all about the missing row 7. I'd hoped to keep everything very simple and aligned between the two tables until I'm ready for more complex endeavors later. Is there a way I can either:

• Bump row 7 (and all subsequent rows) down by one in my spanish_words (so 7 becomes 8, 8 becomes 9, etc)

OR

• Pull up everything after row 6 in english_words?

OR

• Is there a better solution than either of those that you could suggest?

It's possible there's not a way. Originally I'd thought of trying to UPDATE the row 7 data in english_words or maybe insert a new row, but in my research I found an answer on Stack that said you can't insert data into a specific row in the table...and then I realized that's not going to fix anything anyway.

Do those of you more experienced with SQL have any ideas? (Aside from not making such silly mistakes anyway).

Additionally, I'm open to scrapping my tables and starting again, if there's a best-practice that I'm missing. Would setting up a foreign key to correspond between the two tables be a way to fix this? I'm pretty sure you have to do that anyway to perform the joins, but I was going to cross that bridge when I get there. What is best practice amongst database admins - set up foreign keys early on, or later when you need them?

Thanks in advance for your guidance.

Answer 1

A better way to set this up is to create a relation table:

CREATE TABLE translation (
    eng_id int,
    span_id int,
    FOREIGN KEY (eng_id) REFERENCES english_words (eng_id),
    FOREIGN KEY (span_id) REFERENCES spanish_words (span_id)
)

This is better than using a foreign key in the original tables, because you can't have bidirection foreign keys (you have to create the referenced row before the referencing row, so whichever table you insert into first can't have a foreign key pointing to the other one).