• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

What are the limits on WHERE?

 
Don Gardner
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was working on one of the Cattle Drive assignments for JDBC, and I wanted to make sure that my UPDATE and DELETE only affected the single entry they were supposed to affect. However, if I had more than a single parameter on WHERE, the program choked.
If you can only have one WHERE requirement, how do to specify a single entry that has some similar fields?
In the Cattle Drive assignment, the database held movies. How would you distinguish between:
Title |Star
-----------|----------------
Batman |Adam West
and
Title |Star
-----------|----------------
Batman |Micheal Keaton
I suppose you could include the year as part of the title, but I just feel that year relase should be a separate field in the database.
Thanks,
Don
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you may only have one where clause, but you can compound it to narrow down any search to one record.
eg. select title, star,... from [table] where title = 'Batman' and star = 'Adam West' and ......or....etc
I'm not sure what the primary key(s) is(are) in the table, but it should be unique. And on this key you can always differentiate between different records in a database even if all the data is the same.
Jamie
 
Don Gardner
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
> eg. select title, star,... from [table] where title = 'Batman' and
> star = 'Adam West' and ......or....etc
Oh, so you can have additional restrictions on WHERE by using AND/OR. For some reason I thought the additional restrictions were comma seperated.
> I'm not sure what the primary key(s) is(are) in the table, but
> it should be unique. And on this key you can always
> differentiate between different records in a database even if
> all the data is the same.
I'm not sure I understand what you are saying. Are you saying that I could create an additional field that has a unique key for each record in the database, sort of like a HashCode?
[This message has been edited by Don Gardner (edited October 18, 2001).]
 
Johannes de Jong
tumbleweed
Bartender
Posts: 5089
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don a primary key simple means, you have a field that must have a unique value. If title is the primary key , there may only be one Batman in your table.
You also have primary keys made up out of multiple fields.
I suggest you find a decent SQL / database tutorial using Google.
Try these to start off with :
 
Don Gardner
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Primary key! That's what I needed. In my example, I could make Title and Star the primary keys. Doing so would ensure that there would be no duplicate entries.
You would only have problems if there were two movies by the same title with the same star, but that is more of a problem with Hollywood than the JDBC. *grin*
Thanks for the links, JdJ. I'll check them out.
Don
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is just a suggestion of personal preference, rather than a must do, but for primary keys I prefer to use an autonumber/sequence or whatever the database supports. I find that if you don't, some primary keys can start to grow to 4 or 5 columns long. Then when you want to use that primary key in as a foreign key in another table you have to restore all 5 columns in the other table. Using a sequence or autonumber allows you to give each row a unique id, as well as allows for easy foreign key references. I also notice better performance for queries using autonumbers. Then the fields that you want to be unique (eg. Title, Star) I just create constraints to not allow duplicate entries.
I'm curious to see if anyone agrees/disagrees with this.
Jamie
[This message has been edited by Jamie Robertson (edited October 18, 2001).]
 
Adam Hardy
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's standard database design rules. Having a primary key column is important to avoid duplicating data like you said, plus it has advantages for indexing & speed, plus it makes programming SQL queries easier. If you work on a database that has more than 3 or 4 tables, it's a necessity.
Adam
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic