"Table of Constants" - is this common practice?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.