*
The moose likes JDBC and the fly likes SQL - Automatically reflecting the values of 2 columns in another column Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL - Automatically reflecting the values of 2 columns in another column" Watch "SQL - Automatically reflecting the values of 2 columns in another column" New topic
Author

SQL - Automatically reflecting the values of 2 columns in another column

Faisal Ahmad
Ranch Hand

Joined: Aug 31, 2006
Posts: 347

Hi!

I need some help in implementing a SQL query.
I have a table Users with 3 columns: FIRST_NAME, LAST_NAME, FULL_NAME
FIRST_NAME VARCHAR(30) NOT NULL
LAST_NAME VARCHAR(30) NOT NULL
FULL_NAME VARCHAR(60) UNIQUE

I'm inserting values into first 2 columns:
INSERT INTO USERS(FIRST_NAME, LAST_NAME) VALUES ('JAMES', 'GOSLING');

That's perfect. However, here comes interesting part:
The column 'FULL_NAME' should automatically get the value 'JAMES.GOSLING'. Could you please guide me how to get such result? If it's not possible to get this automatically, how could we do that as query? Remember the '.' in between!

Thanks and awaiting for your reply.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Your data model is a a little suspect - why does it need to have full name stored? What benefit does it supply? How have you constrained your table to ensure that full_name is only and always made up of the values in first_name and last_name?

If your table had only two columns (first_name and last_name) is relatively easy to derive a field called full_name:


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Faisal Ahmad
Ranch Hand

Joined: Aug 31, 2006
Posts: 347

First, many thanks for your reply!

Ok, let me rename the FULL_NAME column to USERID. I'm trying to store UNIQUE user ids in this column. In my opinion, the user id should be constructed as:
FIRST_NAME+'.'+LAST_NAME
Notice the '.' in between.

I'm sure the above constraint could easily fail if we've more than one user with the same first name and last name. In such case, I would like to store the user id as:
FIRST_NAME+'.'+MIDDLE_NAME+'.'+LAST_NAME

I'm sure, the above modified constraint would possibly never fail.

I don't know how to write such constraints. I can write query but I would like to specify such logic as part of data model/DDL itself. Could you please tell me if it's possible? If yes, how?

Thanks!
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

I'm sure, the above modified constraint would possibly never fail.
I'm afraid that this is a wrong certainty. I'd steer away from it, and find a real unique key (either using enough columns in your table that make it unique, or by using an auto generated id).

For your initial question: some databases allow you to write triggers that fire upon insert or update of a record. That trigger can compose your FULL_NAME.
You can feel from my explanation above that I am not a fan of this.


OCUP UML fundamental and ITIL foundation
youtube channel
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

So userid is your primary key? In which case, again, why do you need it at all? Could you not just define your two columns as a composite id? Or even better define userid as a surrogate key? The reason I ask is what you are loking for is difficult to implement and I can't seen any reason how it would help your data model?


I'm sure the above constraint could easily fail if we've more than one user with the same first name and last name. In such case, I would like to store the user id as:
FIRST_NAME+'.'+MIDDLE_NAME+'.'+LAST_NAME

I'm fairly sure that can easily fail too. Such are the problems with natural keys - and a good reason to use surrogate keys.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL - Automatically reflecting the values of 2 columns in another column
 
Similar Threads
Identity keys, WebLogic and EJB CMP CMR
msyql jdbc problem.
Column count not matching the amount of columns in database
db-create-table: [java] SQL Error at line 340: java.sql.SQLException: Table not
how to get column information for a given sql string