File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes PKs in two tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "PKs in two tables" Watch "PKs in two tables" New topic

PKs in two tables

induz sat

Joined: Aug 06, 2013
Posts: 18
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

Joined: Mar 01, 2009
Posts: 2289

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

Joined: Aug 06, 2013
Posts: 18
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

Joined: Aug 06, 2013
Posts: 18
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

Don't get me started about those stupid light bulbs.
subject: PKs in two tables
It's not a secret anymore!