Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

College SQL class, using the book "SQL Fundamentals" by John J. Patrick. In the third chapter, he talks about using a "table of constants" to add columns to a select statement, where all rows have the same value.

For example, if you have table "characters", as so:

first_name  last_name  dept_code
----------- ---------- -------------------
Fred        Flintstone ROCKS
Barney      Rubble     ROCKS
Wilma       Flintstone FACEPALMING_AT_FRED

and you want a SELECT that adds a column "hometown" with value "BEDROCK" to all rows, he recommends making a second table in the database, "temp", with

hometown
--------
BEDROCK

and then doing

SELECT first_name, last_name, dept_code, hometown FROM characters, temp

The idea is that this avoids putting string constants in the SELECT statement, and that if you've got a lot of SELECTs that need the same constants, it's easier to update one table than fifty queries.

The thing is, I've been working with SQL databases for the last fifteen years, and I have never seen this construction. Is it something totally common that I've just missed, or is it something that I can erase from my memory after this assignment is over?

share|improve this question

3 Answers 3

up vote 4 down vote accepted

Joe Celko mentions tables of constants in a couple of his books.

He suggests if using a table that a check constraint is added that ensures the table can contain no more than one row.

CREATE TABLE Constants
(
lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY CHECK (lock = 'X'),
pi FLOAT DEFAULT 3.142592653 NOT NULL,
e FLOAT DEFAULT 2.71828182 NOT NULL,
phi FLOAT DEFAULT 1.6180339887 NOT NULL
);

Or alternatively a view can be used for a similar purpose.

CREATE VIEW Constants
AS
SELECT *
FROM 
(VALUES(3.142592653,
        2.71828182,
        1.6180339887)) V(pi,e,phi)

Not something I've used much, if at all, myself but always worth having additional possible techniques to consider.

The specific use case in your question isn't something I would consider a table of constants for though. TBH the hardcoded same "hometown" for everyone just seems a nonsensical requirement.

share|improve this answer
2  
The view solution is pretty nice. It also protects agains accidental changes to the "constants". –  a_horse_with_no_name Mar 27 '12 at 8:52
    
This seems like a much different kind of constant than the OP posted, though. These are application-independent, invariant values. The OP's example is application-dependent data, some of which I'd expect to change over time. It's not clear to me why "hometown" would be considered constant. –  Mike Sherrill 'Cat Recall' Mar 27 '12 at 10:30
    
@Catcall - Agree that seems a bad example. As well as things that are actually constant this could also be used for global variables though. Was thinking about tax rate but probably in real world would want to keep historical rates for this. Maybe could be used if deploying same database to multiple clients for some customisation data. –  Martin Smith Mar 27 '12 at 10:52
1  
I pulled that example directly out of thin air. This may help explain the bad-ness. The one in the book is adding a "last_eval_date" to a bunch of employee records... I still wouldn't do that that way, but maybe better. –  mjfgates Mar 27 '12 at 16:30

If I needed a constant, then I'd consider a simple scalar UDF (SQL Server, but other RDBMS have the same). I've done this to capture "magic numbers" and CR/LF strings and such, if circumstances require this to be in the database

One example from SO: http://stackoverflow.com/a/2947405/27535

The example you gave seems a bit pointless thought. If constant, why add it to a result set? Unless it is a NULL-replacement value or such.

share|improve this answer
    
Enum's I can understand... but this book is explicitly advocating one-row tables, with a single constant in each field, precisely so that you can add constants to the result set. Thus the wtf? in the original question :) –  mjfgates Mar 27 '12 at 16:23

In my experience, I have rarely developed code where a string constant is added to the result set. The proposed solution doesn't make sense to me. I would be highly suspicious of the code quality of a system that did this. Looking at this example I would code it as follows.

first_name  last_name  dept_code
----------- ---------- ---------
Fred        Flintstone 1
Barney      Rubble     1
Wilma       Flintstone 2

dept_code dept_name
--------- -------------------
1         ROCKS
2         FACEPALMING_AT_FRED

SELECT first_name, last_name, dept_name, 'BEDROCK' AS hometown
FROM characters c 
JOIN departments d ON (c.dept_code = d.dept_code )

I would forget his approach after this exercise. For it to work you would need a table or column for each constant or an update before each query. I would hope at some point an address table would be added from which hometown could be selected using an appropriate join.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.