Re: Designing Default Value

From: --CELKO-- <joe.celko_at_northface.edu>
Date: 20 Oct 2003 16:16:39 -0700
Message-ID: <a264e7ea.0310201516.218fb574@posting.google.com>

>> I do NOT want to know about setting default values for CREATE TABLE
statements when adding data, the question is how to design this information so that it can be read into my application. <<

I'll take a guess at this:

CREATE TABLE Foobar
(...
 currency_code CHAR(3) NOT NULL

         REFERENCES Currencies(currency_code),  ...);

CREATE TABLE Currencies
(currency_code CHAR(3) NOT NULL PRIMARY KEY,  priority_nbr INTEGER NOT CHECK (priority_nbr IN (1,0),

• the following is SQL-92, but not in all products yet CHECK ((SELECT SUM (priority_nbr) FROM Currencies) = 1), ...);

Then use a scalar subquery to find the #1 code when a NULL is given instead of an explicit currency code to the input procedure:

INSERT INTO Foobar
 VALUES (..., COALESCE (:explicit_curency_code,

                        (SELECT curency_code FROM Currencies WHERE

priority_nbr = 1)), ...); Received on Mon Oct 20 2003 - 18:16:39 CDT