• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

one to many relationship in sqlite

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I'm having difficulties while learn about one to many realtionship which will be implemented in sqlite. Let's say i have table A that contains orderid(as primary key), userid, password, money, balance. Table B contains orderid_FK(as foreign key from table A), price, num_order, subtotal, foodid. one to many relationship here means one table A associated with multiple table B. what i've got from googling... foreign key is merely stating a relationship between table, so when if orderid = 1 then the orderid_FK values won't be automatically 1. how can i  make the condition if orderid = 1 then order_FK values also =1 automatically? I've read a few references that saying that i can use 'cascade update' but using 'cascade' i still had to insert orderid_FK manually. Please correct me if i'm wrong, i'm selftaugh and english is't my first language so i may misread some information. Thanks.  
 
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
When there is a one-to-many relationship between two tables, the "many" table contains the key of the "one" table, plus some sort of data that is extra.  So for an order to order detail relationship, you would have the order_id in the order table and order_id + seq_no as the key to the order detail table. Visually, it would look like this:

   order table
   order_id

   order_detail
   order_id
   sequence_number
   FK to order table, order_id


You may have already known this, but just to be sure I illustrated it here.

So now I think your question is how to auto-increment sequence_number.  You just add AUTOINCREMENT to (in this case) the sequence_number.  I've read a few articles saying that AUTOINCREMENT should be avoided, but it's what I use, and I'm not sure how to do the same thing without it.
 
Saloon Keeper
Posts: 27763
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Knute Snortum wrote:I've read a few articles saying that AUTOINCREMENT should be avoided, but it's what I use, and I'm not sure how to do the same thing without it.



I think that in some versions of some databases, autoincrement is not a language feature. And in some cases, there are different types of auto-generated fields possible, not just auto-incremented integers. And different means have been used (such as Oracle's DUAL table) to create them. How much of this is now normalized and portable across platforms and versions, I don't know - haven't looked recently. But it's stuff like that that has made it difficult to use autoincrement.

On the other hand, as far as I'm concerned, if your platform support it, I recommend taking advantage of it.

Now there have been platforms and projects where I couldn't assign a unique primary key value until fairly late in the process and had to do it manually. And some platforms where the key would be generated but it was difficult or impossible to tell what the generated key value actually was. But if you don't have those problems, go for it!
 
Dina haniza
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Knute Snortum wrote:When there is a one-to-many relationship between two tables, the "many" table contains the key of the "one" table, plus some sort of data that is extra.  So for an order to order detail relationship, you would have the order_id in the order table and order_id + seq_no as the key to the order detail table. Visually, it would look like this:

   order table
   order_id

   order_detail
   order_id
   sequence_number
   FK to order table, order_id


You may have already known this, but just to be sure I illustrated it here.

So now I think your question is how to auto-increment sequence_number.  You just add AUTOINCREMENT to (in this case) the sequence_number.  I've read a few articles saying that AUTOINCREMENT should be avoided, but it's what I use, and I'm not sure how to do the same thing without it.




i'm not trying to add auto increment in my foreign key (i'm using auto increment in primary key a.k.a orderid). what i've want is this :

table order
orderiduser
1joan


table order detail
orderid_fkfoodidnum_order
1chocolate5
1marshmallow6


i wan't joan can buy chocolate and marshmallow (orderid has same value with orderid_fk).So, if there's a new people want to order the database will be look like this:

table order
orderiduser
1joan
2jane


table order detail
orderid_fkfoodidnum_order
1chocolate5
1marshmallow6
2chocolate5
2orange_juice6
 
Knute Snortum
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Okay, I was confused.  Let's look at some actual SQL DDL:
I've taken the liberty of renaming some columns.  First, your orderid in the table order I changed to id.  You know it's an order id by the table name.  Next, the order_detail table has three columns: order_id, order_number, and food_item.  The first two are the key.  order_id references order (id) as a foreign key.

Now, how do you "automatically" update the order_id in order_detail?  You can't.  It's your responsibility to create a row with all the columns you need.  Unless I'm misunderstanding what you mean by "automatically."
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic