Because I want insert data to the child table.
Do you mean, I can handle id system maually? I will try it... I try break connection between the master and child, and then I add data to the child table, I insert to the correct parent id field, did you mean?
You wouldn't even need to break the connection between master and child. You could just use another SQLQuery or even use Sqlite3Dataset1.ExecuteDirect with an insert statement. If you insert the master record you should be able to use Sqlite3Dataset1.LastInsertRowId to get your last inserted contactID (if contactID is an autoincrement column in your table). After that you could add the phone-records with the just saved ContactID.
It's also a good practice delete all phones together when a contact is deleted. To accomplish this create a BeforeDelete event handler in ContactsDataset and put this code:
while not PhonesDataset.IsEmpty do
PhonesDataset.Delete;
Ieks
Yes. Deleting all Phonenumbers is good practice. But what if there is a problem. You might end up with a deleted master and partially deleted phonenumer. But some phonenumber may still be there if your program crashes or something.
Good database-design has build in FOREIGN KEYS. Do you have a FOREIGN KEY in your phonenumber table to your contacts table? If not, that could lead to problems. You could delete the contact without deleting the phonenumbers, leaving the phonenumbers without a contact. Ieks
You have two options with foreign keys (but for a good database you need at least one of them):
Just declare a foreign key in phones like this:
CREATE TABLE phones(
phoneid INTEGER,
contactid INTEGER,
phonenumber TEXT,
FOREIGN KEY(contactid) REFERENCES contacts(contactid)
);
In that case when you try to delete a contact which still had phonenumbers, it fails (as it should). You would need to delete all phonenumers first (manually via code) before deleting the contact.
The second option is easier. The database can delete the phonenumbers for you when you delete the contact. In that case you would add ON DELETE CASCADE to the FOREIGN KEY-line.
FOREIGN KEY(contactid) REFERENCES contacts(contactid) ON DELETE CASCADE
So this is just a way to keep your database consistent.
For now you could do it manually in code but when you get the chance you should look into adding foreign key constraints to your database.
A quick sidenote (which would make all the above meaningless):
I noticed that the documentation of SQLite says you need to enable foreign keys in your application:
Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command.
Does anybody know if this is still the case? Does it require extra commands to enable the foreign key support in sqlite? (I haven't tested this myself because my main database-engine is Firebird)