This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes PKs in two tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PKs in two tables" Watch "PKs in two tables" New topic
Author

PKs in two tables

induz sat
Greenhorn

Joined: Aug 06, 2013
Posts: 15
I have 2 tables in a Socialnetwork schema.
[1] registredUser- userId, userName, Password, age, sex,city- userId is PK and Auto Increment
[2] userinfo- userId, userName Password- userId is Pkey and Auto Incremnt

When a user login on a login.jsp, he submits username and Pasword.

These two parameters of login.jsp are checked in registeredUser Table. If matched, he could log in.

If he is unable to log in he has to register in registration.jsp supplying username, password, Age, sex and city parametsrs. These records are added to the RegistredUser table.

How can I add username and password in the userInfo table when the user has successful log in so that next time the check has to be in userinfo table only, not in registred table?


chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1607
    
  13

Why do you have two tables containing the same information? What is the "userInfo" table for? This data all relates to a single logical entity - a specific user - so it should normally be stored in the same record in the same table. You can always use SQL views with different permissions to restrict visibility of certain columns, such as password. Relational data models are designed to store each data value just once, except for foreign keys, so there is no need to duplicate information like this (de-normalisation is a different issue).

If you have to have two tables containing information relating to the same logical entity e.g. to keep the password in a completely separate table, then this is effectively a 1:1 relationship, so both table records should have the same PK value for a given logical record (user). You would therefore create the PK in the first table A, e.g. using auto-increment, then use that value as the PK for the other table B. Create a foreign key from B back to A to enforce the relationship. Don't create different PKs for the same logical record in the two tables.


No more Blub for me, thank you, Vicar.
induz sat
Greenhorn

Joined: Aug 06, 2013
Posts: 15
Thanks for the clarifications about 2 tables and PKs.
userId in Table 1[registeredUser] is the PK, that would be the same in the userInfo table. I could have stored all the records in a table[ registredUser] but for my learning I want to have 2 tables.

Now the big Q is when the user is being registered through Registration and UserRegistration servlet, how could I keep the PK FK relationship among both tables.

Column name, userId, is the same in both the Tables. userId in registredUser is PK; but I am not sure about its FK realtionship in userInfo

Hope to get some heads-up.

induz sat
Greenhorn

Joined: Aug 06, 2013
Posts: 15
What should be the query for checking userName and Password from registredUser table to be valid and matching with the Parameters[ 'user' and 'pw' ] from Login.jsp.
I am getting error and query is not working...please help

 
 
subject: PKs in two tables
 
Similar Threads
Getting values from mySQL table using JDBC
Updating/inserting data through servlet using JDBC
foreign key related problem
servlet, mysql JDBC connection and Parameters
checking parameters from a jsp against mySQL table