Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and the fly likes What are the limits on WHERE? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "What are the limits on WHERE?" Watch "What are the limits on WHERE?" New topic
Author

What are the limits on WHERE?

Don Gardner
Greenhorn

Joined: Oct 09, 2001
Posts: 25
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

Joined: Jul 09, 2001
Posts: 1879

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

Joined: Oct 09, 2001
Posts: 25
> 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

Joined: Jan 27, 2001
Posts: 5089
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

Joined: Oct 09, 2001
Posts: 25
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

Joined: Jul 09, 2001
Posts: 1879

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

Joined: Oct 09, 2001
Posts: 566
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


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
 
Don't get me started about those stupid light bulbs.
 
subject: What are the limits on WHERE?