• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Faisal Ahmad
Ranch Hand
Posts: 355
Chrome Java Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

 
Faisal Ahmad
Ranch Hand
Posts: 355
Chrome Java Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic