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


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?

3 Answers 3


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.

pi FLOAT DEFAULT 3.142592653 NOT NULL,
phi FLOAT DEFAULT 1.6180339887 NOT NULL

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

        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.

  • 2
    The view solution is pretty nice. It also protects agains accidental changes to the "constants".
    – user1822
    Mar 27, 2012 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. Mar 27, 2012 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. Mar 27, 2012 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, 2012 at 16:30

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

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.


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: https://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.

  • 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, 2012 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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