List:General Discussion« Previous MessageNext Message »
From:Christian Hammers Date:November 5 2006 9:28pm
Subject:Re: Default Column Value
View as plain text  
Hi

On 2006-11-05 Bill Guion wrote:
> In one of my tables I have a start_date - timestamp (2007-07-04). A 
> separate column, start_yr_mo, has 200704 (first seven characters of 
> timestamp without the '-'). Both are entered manually. Can I define 
> start_yr_mo as a default of, for example, set start_yr_mo = 
> concat(substr(start_date, 1, 4),substr(start_date,6,2)). Something 
> like

You can use a "VIEW" for this:

CREATE VIEW 
  view_table (start_date, start_yr_mo) 
AS SELECT 
  start,
  concat(substr(start_date, 1, 4),substr(start_date,6,2)) 
FROM 
  orig_table
;

mysql> SELECT * FROM view_table;
+-----------------+-------------+
| start_date      | start_yr_mo |
+-----------------+-------------+
| 2007-07-04      | 200707      | 
+-----------------+-------------+
1 row in set (0.00 sec)


Alternatively one could use a "TRIGGER" that updates the start_yr_mo
whenever start_date is modified but I'm unsure if triggers are present
before 5.1.

Of course the best way could be to drop the start_yr_mo completely and let
the application render it. Storing redundant data in a database is seldom
a good idea and using SQL for formatting neither.

bye,

-christian-
Thread
Default Column ValueBill Guion5 Nov
  • Re: Default Column ValueChristian Hammers5 Nov
  • Re: Default Column ValueMartijn Tonies6 Nov