Re: Primary key on one or two fields

>> Does anyone know the language of the SQL99 standards in relation
to default values defined on primary key columns? <<

It is allowed, same as in SQL-92.

if you want to have some fun with DEFAULT, this try as a way to model a class structure in SQL:

CREATE TABLE Foobar
(foo_key INTEGER NOT NULL UNIQUE
foo_flag CHAR(1) DEFAULT 'A' NOT NULL

         CHECK(foo_flag IN ('A', 'B')),
...
PRIMARY KEY (foo_key, foo_flag));

CREATE TABLE Foo_A
(foo_key INTEGER NOT NULL UNIQUE
foo_flag CHAR(1) DEFAULT 'A' NOT NULL

         CHECK(foo_flag = 'A'),
...
FOREIGN KEY (foo_key, foo_flag)
REFERENCES Foobar (foo_key, foo_flag),
PRIMARY KEY (foo_key, foo_flag));

CREATE TABLE Foo_B
(foo_key INTEGER NOT NULL UNIQUE
foo_flag CHAR(1) DEFAULT 'B' NOT NULL

         CHECK(foo_flag = 'B'),
...
FOREIGN KEY (foo_key, foo_flag)
REFERENCES Foobar (foo_key, foo_flag),
PRIMARY KEY (foo_key, foo_flag));

Or one of my favorites:

CREATE TABLE UniversalConstants
(lock_flag CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY

           CHECK (lock_flag = 'X'),
 pi FLOAT DEFAULT 3.14152653... NOT NULL,  e FLOAT DEFAULT 2.71828 ... NOT NULL,
 phi FLOAT DEFAULT 1.6180339... NOT NULL,  ...);

now use that little-known option:

INSERT INTO UniversalConstants DEFAULT VALUES;

and you have a table of constants which you can protect by giving PUBLIC a VIEW without the lock_flag column in it. Received on Thu Aug 14 2003 - 22:10:41 CEST