I just completed this exercise. One question I had about it. How do you re-index the rows after you remove one? Step #8 wants you to Wait, Jane Smith is not a real person. Remove her from friends.. What this does is cause this in the picture below.
/*Create a table named friends with three columns:
id that stores INTEGER
name that stores TEXT
birthday that stores DATE*/
CREATE TABLE friends ( id INTEGER, name TEXT, birthday DATE);
/*Beneath your current code, add Jane Doe to friends.Her birthday is May 30th, 1990.*/
INSERT INTO friends ( id, name, birthday) VALUES ( 1, 'Jane Doe', '1990-05-30');
/*Let’s make sure that Jane has been added to the database: SELECT * FROM friends;*/
/*Add two of your friends to the table. Insert an id, name, and birthday for each of them.*/
INSERT INTO friends ( id, name, birthday) VALUES ( 2, 'Philip', '1980-05-30');
INSERT INTO friends ( id, name, birthday) VALUES ( 3, 'Scott', '1970-05-30');
/*Jane Doe just got married! Her new last name is “Smith”.Update her record in friends.*/
UPDATE friends SET name = 'Jane Smith' WHERE id = 1;
/*Add a new column named email.*/
ALTER TABLE friends ADD email TEXT;
/*Update the email address for everyone in your table.*/
UPDATE friends SET email = 'email@example.com' WHERE id = 1;
UPDATE friends SET email = 'firstname.lastname@example.org' WHERE id = 2;
UPDATE friends SET email = 'email@example.com' WHERE id = 3;
/*Wait, Jane Smith is not a real person. Remove her from friends.*/
DELETE FROM friends WHERE id = 1;
/*Great job! Let’s take a look at the result one last time:*/
SELECT * FROM friends;
Well, the row they wanted you to remove was 1, as in id 1. So, when you do so, it left row 2, 3, 4, in place. It did not re-index the table. So, how could I re-index the table so that 2 = 1, 3 =2, 4 =3?
OK, so lets say we have a table. id is used here to enumerate the row. We delete a row. How is that to be handled in a real world situation? Is this normal to have NULL rows in a database? I’m just having a hard time seeing how you would just delete a row and forget about it? I can understand if it was sorted by like a name, so it didn’t matter, but when its numerical like this, I would think it would matter?
Think I see your point now and how I am overthinking this or thinking in the wrong direction. In this exercise id is simply that. It’s not an index.
This question came to mind because of past use of SQLite on a video game server I have developed for many years. It has SQLite, and one feature was a PvP system. Basically fighting. So you would log win/loss/ratio in short among other things. Well, lets say you ban a player, and you want to purge all there records. OK so we remove those rows. Well, in this case, we need every row sorted without any skipping in the index column. Sure, I could use an app to repack the database. But what if we wanted to do so by script after the ban was in place.
Hope that all makes sense and to give you an idea where I am coming from with this line of thought.
makes me wonder both why they’d need to have some consecutive numbering (I get the feeling it really shouldn’t matter, what would that be used for, is that a bug/bad idea) and also makes me wonder why they’d need to get removed if they’re banned, overkill
Toxic players exist in gaming. Sometimes no matter what you do, the only solution is to ban them. I try my best to script around such behavior to prevent unwanted behavior, but sometimes either its not possible, or requires immediate attention.
As for the consecutive numbering, imagine a scoreboard/ranking system. You wouldn’t want your ranking to be non-sequential or have gaps in it.
That’s sounds like a better idea. That is, to have a new column IsBanned and omit all results that return true in that column when you query the table. That way, if you wish to un-ban someone, the data’s integrity remains.