Re: [sqlite] Updating key on related table
On 15/12/2011, at 2:58 PM, Jeff Matthews wrote: > Thanks. You are putting me on track. > > Can you describe the properties, collate, nocase, restrict, etc., The "primary key" constraint automatically assigns a new ID to each Customer and Invoice when you insert a new row that doesn't specify a value for it. The "on delete restrict" constraint on the Invoice prevents deleting any Customer which has invoices. The "collate nocase" ensures that nocase is used to compare Customer Name, so 'tom' is treated as equal to 'Tom'. > or better yet, point me to a reference that contains a full set of features, > including these? It's just standard SQL. You can find the syntax described on the SQLite website here: http://www.sqlite.org/lang_createtable.html Here's a comparison of some GUI software that does SQLite design and data entry: http://www.barefeetware.com/sqlite/compare/?ml Please send replied to this mail list, not me directly. Thanks, Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Twitter: http://twitter.com/barefeetware/ Facebook: http://www.facebook.com/BareFeetWare ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updating key on related table
On 15/12/2011, at 2:11 PM, Jeff Matthews wrote: > Customer Table > >ID >Name > > Invoice Table > >CustomerID >InvoiceNumber > > When I create a new invoice record for the selected customer, does > Invoice.CustomerID update with Customer.ID automatically, or do I need to do > this manually? If it's automatic, can someone explain how it does it > automatically? What do you mean by "the selected customer"? It depends how you've set it up in your schema. For instance, if your schema is: create table "Customer" ( ID integer primary key not null , Name text collate nocase not null ) ; create table "Invoice" ( ID integer primary key not null , CustomerID integer references "Customer" (ID) on delete restrict on update cascade ) ; then: insert into "Invoice" (CustomerID) select max(ID) from "Customer"; will create a new Invoice assigned to the most recently added Customer (assuming no deletions). or: insert into "Invoice" (CustomerID) select ID from "Customer" where "Name" = 'Tom'; will create a new invoice assigned to the customer whose name is 'Tom'. Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Twitter: http://twitter.com/barefeetware/ Facebook: http://www.facebook.com/BareFeetWare ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Updating key on related table
Customer Table ID Name Invoice Table CustomerID InvoiceNumber When I create a new invoice record for the selected customer, does Invoice.CustomerID update with Customer.ID automatically, or do I need to do this manually? If it's automatic, can someone explain how it does it automatically? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
