| Computing desk | ||
|---|---|---|
| < October 2 | << Sep | October | Nov >> | October 4 > |
| Welcome to the Wikipedia Computing Reference Desk Archives |
|---|
| The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages. |
October 3 Information
I have a database with some humans who each own zero or more dogs. Every dog has a human owner, so the schema is something like:
CREATE TABLE dogs (rowid PRIMARY KEY INTEGER, name TEXT, birthdate DATE, owner FOREIGN KEY (human));
(I might not have the above syntax exactly right), and similarly there's a humans table with names and addresses or whatever, plus rowids. The dog owner column will contain the rowid of a a row in the humans table.
I'd like to add a new human named Bob, and Bob's dog Fido to the database. That means I have to insert two rows (in a transaction but that shouldn't be relevant). I do an INSERT statement for Bob and another one for Fido. But to insert Fido's row, I need Bob's rowid.
The INSERT statement doesn't give back the rowid it has created. Do I actually have to do a total of three statements, two inserts separated by a SELECT to get back Bob's rowid? How do I know that the rowid I got is the row that I just inserted (maybe there is already another Bob in the database), etc.? Thanks.
If it matters, I mostly use sqlite3 right now, but will probably want to start using postgres and maybe mysql at some point. I'm unlikely to ever care about MS or Oracle.
173.228.123.207 ( talk) 00:47, 3 October 2019 (UTC)
- Yes, unless the insert statement for the human name actually spits out the ROW_ID, you would need to do a select for it. To make sure you avoid getting the wrong Bob, repeat all of the fields used in the insert statement, like his name, birth-date, address, etc. Normally you wouldn't want to search based on all those things, since a small typo would cause you to miss the desired record, but if this is all automated, it's trivial to repeat the same exact fields just used in the insert statement. Note that you don't have to manually handle the ROW_ID, it can be used directly in the insert statement for the dog:
INSERT NAME = " "
BDATE = " " ...
OWNER_ROW_ID = (SELECT ROW_ID FROM OWNER WHERE ... );
INTO DOG;
- SinisterLefty ( talk) 01:36, 3 October 2019 (UTC)
- Thanks, I thought maybe I had missed something. Selecting by repeating all the data seems a bit hacky since in general it might not be guaranteed to be unique. Even if it's the same Bob, maybe he signed up twice with the same info both times. All the reliable ways I can think of seem terrible. I'll puzzle over the manual and see if I can figure out anything, hopefully not too db dependent. 173.228.123.207 ( talk) 08:54, 3 October 2019 (UTC)
- You should define unique keys on both tables to ensure that they can't have duplicate entries. The unique key can comprise multiple fields, although typically a single unique field is used, like a Social Security number or Driver's License number or phone number. SinisterLefty ( talk) 15:47, 3 October 2019 (UTC)
- For sqlite3, use Last Insert Rowid rather than searching for the entry you just added. I assume you are checking that the first insertion worked, otherwise you might get an unexpected value. I expect any other SQL database would have a similar function.- gadfium 21:20, 3 October 2019 (UTC)
- Good hint. But avoiding duplicate rows in both tables by using unique keys still should be done, in any case.
SinisterLefty (
talk) 21:31, 3 October 2019 (UTC)
- Rowids are great for transactional linkage, but "If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY." See the sqlite documentation. Other databases may also change the rowids, particularly during dump/restore processing. Do as SinisterLefty says and implement a primary key. Martin of Sheffield ( talk) 22:14, 3 October 2019 (UTC)
- Good hint. But avoiding duplicate rows in both tables by using unique keys still should be done, in any case.
SinisterLefty (
talk) 21:31, 3 October 2019 (UTC)
- Thanks, last_insert_rowid sounds like what I want. Using UNIQUE KEY on the actual data doesn't seem nice, since it's "in-band signalling" so to speak. Adding a column would work but it means adding a column, not so good. I'll check whether other db's have something like last_insert_rowid. It seems weird that there's no standard SQL thing for it. 173.228.123.207 ( talk) 01:58, 4 October 2019 (UTC)
- Just to repeat, having duplicate rows will cause you no end of problems. Try to change the billing address ? You may change one copy but have others with the old address. Then when you go to send bills, who knows which address will pop up.
SinisterLefty (
talk) 03:23, 4 October 2019 (UTC)
- Sure, but that's application dependent. It's best to separate the concerns, which last_insert_rowid does. It turns out mysql and pg do have equivalents though they aren't identical (I don't remember exactly what they were). So I think this is the right approach. Your point about duplicated data being bad is still valid of course. 173.228.123.207 ( talk) 08:04, 4 October 2019 (UTC)
- Just to repeat, having duplicate rows will cause you no end of problems. Try to change the billing address ? You may change one copy but have others with the old address. Then when you go to send bills, who knows which address will pop up.
SinisterLefty (
talk) 03:23, 4 October 2019 (UTC)
I use several text editors, including Notepad++ and MS Word, and I'm constantly selecting text to change the font, etc. I can double-click on a word/phrase to select it, but the selection won't extend beyond the typical delimiters (i.e. period (.), dash (-), etc.). I'm a programmer, so a lot of the words I'm highlighting are things like "test-object-1", where ideally I'd like double-clicking to select the entire object instead of just one of the three "words". Is there a way to do that, possibly an add-on that I could install? I typically work on both Windows and Linux machines. OldTimeNESter ( talk) 15:05, 3 October 2019 (UTC)
- One hint is to use underscores instead of dashes for your object names: test_object_1. Or you can double click to select the first word, keep the mouse held down, then drag the mouse to select the rest. SinisterLefty ( talk) 15:59, 3 October 2019 (UTC)
Is there a way to make it so no web sites can even ask if I want notifications ? I don't ever want them, and find being asked the question, repeatedly, annoying. SinisterLefty ( talk) 18:32, 3 October 2019 (UTC)
- @ SinisterLefty: Here's one way: [1] RudolfRed ( talk) 18:41, 3 October 2019 (UTC)
- Thanks. I had seen that toggle before that was labeled "Notifications: Ask before sending (recommended)". I had that set to "on" because I thought the (non-recommended) alternative would be for web sites to be able to send notifications without asking me if I wanted them. But, to my surprise, the alternative appears to be "Blocked", which must mean not to send notifications at all. Very confusing. SinisterLefty ( talk) 19:35, 3 October 2019 (UTC)
| Computing desk | ||
|---|---|---|
| < October 2 | << Sep | October | Nov >> | October 4 > |
| Welcome to the Wikipedia Computing Reference Desk Archives |
|---|
| The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages. |
October 3 Information
I have a database with some humans who each own zero or more dogs. Every dog has a human owner, so the schema is something like:
CREATE TABLE dogs (rowid PRIMARY KEY INTEGER, name TEXT, birthdate DATE, owner FOREIGN KEY (human));
(I might not have the above syntax exactly right), and similarly there's a humans table with names and addresses or whatever, plus rowids. The dog owner column will contain the rowid of a a row in the humans table.
I'd like to add a new human named Bob, and Bob's dog Fido to the database. That means I have to insert two rows (in a transaction but that shouldn't be relevant). I do an INSERT statement for Bob and another one for Fido. But to insert Fido's row, I need Bob's rowid.
The INSERT statement doesn't give back the rowid it has created. Do I actually have to do a total of three statements, two inserts separated by a SELECT to get back Bob's rowid? How do I know that the rowid I got is the row that I just inserted (maybe there is already another Bob in the database), etc.? Thanks.
If it matters, I mostly use sqlite3 right now, but will probably want to start using postgres and maybe mysql at some point. I'm unlikely to ever care about MS or Oracle.
173.228.123.207 ( talk) 00:47, 3 October 2019 (UTC)
- Yes, unless the insert statement for the human name actually spits out the ROW_ID, you would need to do a select for it. To make sure you avoid getting the wrong Bob, repeat all of the fields used in the insert statement, like his name, birth-date, address, etc. Normally you wouldn't want to search based on all those things, since a small typo would cause you to miss the desired record, but if this is all automated, it's trivial to repeat the same exact fields just used in the insert statement. Note that you don't have to manually handle the ROW_ID, it can be used directly in the insert statement for the dog:
INSERT NAME = " "
BDATE = " " ...
OWNER_ROW_ID = (SELECT ROW_ID FROM OWNER WHERE ... );
INTO DOG;
- SinisterLefty ( talk) 01:36, 3 October 2019 (UTC)
- Thanks, I thought maybe I had missed something. Selecting by repeating all the data seems a bit hacky since in general it might not be guaranteed to be unique. Even if it's the same Bob, maybe he signed up twice with the same info both times. All the reliable ways I can think of seem terrible. I'll puzzle over the manual and see if I can figure out anything, hopefully not too db dependent. 173.228.123.207 ( talk) 08:54, 3 October 2019 (UTC)
- You should define unique keys on both tables to ensure that they can't have duplicate entries. The unique key can comprise multiple fields, although typically a single unique field is used, like a Social Security number or Driver's License number or phone number. SinisterLefty ( talk) 15:47, 3 October 2019 (UTC)
- For sqlite3, use Last Insert Rowid rather than searching for the entry you just added. I assume you are checking that the first insertion worked, otherwise you might get an unexpected value. I expect any other SQL database would have a similar function.- gadfium 21:20, 3 October 2019 (UTC)
- Good hint. But avoiding duplicate rows in both tables by using unique keys still should be done, in any case.
SinisterLefty (
talk) 21:31, 3 October 2019 (UTC)
- Rowids are great for transactional linkage, but "If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY." See the sqlite documentation. Other databases may also change the rowids, particularly during dump/restore processing. Do as SinisterLefty says and implement a primary key. Martin of Sheffield ( talk) 22:14, 3 October 2019 (UTC)
- Good hint. But avoiding duplicate rows in both tables by using unique keys still should be done, in any case.
SinisterLefty (
talk) 21:31, 3 October 2019 (UTC)
- Thanks, last_insert_rowid sounds like what I want. Using UNIQUE KEY on the actual data doesn't seem nice, since it's "in-band signalling" so to speak. Adding a column would work but it means adding a column, not so good. I'll check whether other db's have something like last_insert_rowid. It seems weird that there's no standard SQL thing for it. 173.228.123.207 ( talk) 01:58, 4 October 2019 (UTC)
- Just to repeat, having duplicate rows will cause you no end of problems. Try to change the billing address ? You may change one copy but have others with the old address. Then when you go to send bills, who knows which address will pop up.
SinisterLefty (
talk) 03:23, 4 October 2019 (UTC)
- Sure, but that's application dependent. It's best to separate the concerns, which last_insert_rowid does. It turns out mysql and pg do have equivalents though they aren't identical (I don't remember exactly what they were). So I think this is the right approach. Your point about duplicated data being bad is still valid of course. 173.228.123.207 ( talk) 08:04, 4 October 2019 (UTC)
- Just to repeat, having duplicate rows will cause you no end of problems. Try to change the billing address ? You may change one copy but have others with the old address. Then when you go to send bills, who knows which address will pop up.
SinisterLefty (
talk) 03:23, 4 October 2019 (UTC)
I use several text editors, including Notepad++ and MS Word, and I'm constantly selecting text to change the font, etc. I can double-click on a word/phrase to select it, but the selection won't extend beyond the typical delimiters (i.e. period (.), dash (-), etc.). I'm a programmer, so a lot of the words I'm highlighting are things like "test-object-1", where ideally I'd like double-clicking to select the entire object instead of just one of the three "words". Is there a way to do that, possibly an add-on that I could install? I typically work on both Windows and Linux machines. OldTimeNESter ( talk) 15:05, 3 October 2019 (UTC)
- One hint is to use underscores instead of dashes for your object names: test_object_1. Or you can double click to select the first word, keep the mouse held down, then drag the mouse to select the rest. SinisterLefty ( talk) 15:59, 3 October 2019 (UTC)
Is there a way to make it so no web sites can even ask if I want notifications ? I don't ever want them, and find being asked the question, repeatedly, annoying. SinisterLefty ( talk) 18:32, 3 October 2019 (UTC)
- @ SinisterLefty: Here's one way: [1] RudolfRed ( talk) 18:41, 3 October 2019 (UTC)
- Thanks. I had seen that toggle before that was labeled "Notifications: Ask before sending (recommended)". I had that set to "on" because I thought the (non-recommended) alternative would be for web sites to be able to send notifications without asking me if I wanted them. But, to my surprise, the alternative appears to be "Blocked", which must mean not to send notifications at all. Very confusing. SinisterLefty ( talk) 19:35, 3 October 2019 (UTC)