Is "CREATE DOMAIN" in 6.3 ??

On Sat, 28 Feb 1998, al dev wrote:

Hi:
Is create domain command implemented in 6.3??
I am trying to use
create domain employed as char(10)
check (
value = "YES" or
value = "NO" or
value = "RETIRED" or
value = "DISABLED" or
value is NULL
);
in SQL scripts but is failing in 6.2.1 postgresql.

I can find work around BUT there are tons of create domains in my SQL
scripts and will be very tedious.
By the way, create domain is in defined in SQL 92
see this chapter 42 in
http://sunsite.unc.edu/LDP/HOWTO/Database-HOWTO.html

I took a look here, and it didn't say (at least not in chapter
42)...what exactly does 'create domain' do? We don't, and won't, have it
for v6.3, not with a release in a few days, and since I do recall anyone
else having mentioned it before, it isn't on our TODO list, but sounds
like something else to be added...

But, a short description of what it does would be nice, as I've
never heard of that one before :)

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

At 22:10 +0200 on 28/2/98, The Hermit Hacker wrote:

I took a look here, and it didn't say (at least not in chapter
42)...what exactly does 'create domain' do? We don't, and won't, have it
for v6.3, not with a release in a few days, and since I do recall anyone
else having mentioned it before, it isn't on our TODO list, but sounds
like something else to be added...

But, a short description of what it does would be nice, as I've
never heard of that one before :)

The idea, I think, is to define datatypes so that fields which are supposed
logically to be of the same kind will all have the same domain. Thus, if
the domain is later changed (say, implementation is changed from money to
float), all fields defined that way are changed together.

This is from SQL 1992:

A domain is a set of permissible values. A domain is defined in
a schema and is identified by a <domain name>. The purpose of a
domain is to constrain the set of valid values that can be stored
in SQL-data by various operations.

A domain definition specifies a data type. It may also specify a
<domain constraint> that further restricts the valid values of the
domain and a <default clause> that specifies the value to be used
in the absence of an explicitly specified value or column default.

A domain is described by a domain descriptor. A domain descriptor
includes:

- the name of the domain;

- the data type descriptor of the data type of the domain;

- the <collation name> from the <collate clause>, if any, of the
domain;

- the value of <default option>, if any, of the domain; and

- the domain constraint descriptors of the domain constraints, if
any, of the domain.

The syntax:

<domain definition> ::=
CREATE DOMAIN <domain name> [ AS ] <data type>
[ <default clause> ]
[ <domain constraint>... ]
[ <collate clause> ]

<domain constraint> ::=
[ <constraint name definition> ]
<check constraint definition> [ <constraint attributes> ]

I won't quote the entire syntax rules from SQL 1992 - I'm sure someone out
there has a copy. In any case, this is NOT Entry-Level SQL 1992, but rather
Intermediate Level.

Herouth

The purpose of using 'create domain' is as given
in the example below. I defined domain name 'EMPLOYED'
and use in create table - see the field 'EMPLOYED' is
of data-type EMPLOYED:
CREATE TABLE EMPLOYER (
PERSON_ID INTEGER NOT NULL,
EMPLOYER VARCHAR(60),
EMPLOYED EMPLOYED,
^^^^^^^^^^
UNIQUE (PATIENT_ID));

The datatype employed is defined by domain which also
restricts the values to "YES" or "NO" or "RETIRED" or "DISABLED" or
NULL.

al

---The Hermit Hacker <scrappy@hub.org> wrote:

On Sat, 28 Feb 1998, al dev wrote:

Hi:
Is create domain command implemented in 6.3??
I am trying to use
create domain employed as char(10)
check (
value = "YES" or
value = "NO" or
value = "RETIRED" or
value = "DISABLED" or
value is NULL
);
in SQL scripts but is failing in 6.2.1 postgresql.

I can find work around BUT there are tons of create domains in my

SQL

scripts and will be very tedious.
By the way, create domain is in defined in SQL 92
see this chapter 42 in
http://sunsite.unc.edu/LDP/HOWTO/Database-HOWTO.html

I took a look here, and it didn't say (at least not in chapter
42)...what exactly does 'create domain' do? We don't, and won't,

have it

for v6.3, not with a release in a few days, and since I do recall

anyone

else having mentioned it before, it isn't on our TODO list, but sounds
like something else to be added...

But, a short description of what it does would be nice, as I've
never heard of that one before :)

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary:

scrappy@{freebsd|postgresql}.org

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Sun, 1 Mar 1998, al dev wrote:

The purpose of using 'create domain' is as given
in the example below. I defined domain name 'EMPLOYED'
and use in create table - see the field 'EMPLOYED' is
of data-type EMPLOYED:
CREATE TABLE EMPLOYER (
PERSON_ID INTEGER NOT NULL,
EMPLOYER VARCHAR(60),
EMPLOYED EMPLOYED,
^^^^^^^^^^
UNIQUE (PATIENT_ID));

The datatype employed is defined by domain which also
restricts the values to "YES" or "NO" or "RETIRED" or "DISABLED" or
NULL.

Oh, cool...so, essentially, you are creating an enumerated(?) type
to be used in a table?

Bruce, can you add this onto the TODO list for v6.4? This is
something that we might be able to do now with triggers, no? But, the
CREATE DOMAIN is part of the spec... :)

On Sun, Mar 01, 1998 at 03:01:12PM -0500, The Hermit Hacker wrote:

The datatype employed is defined by domain which also
restricts the values to "YES" or "NO" or "RETIRED" or "DISABLED" or
NULL.

Oh, cool...so, essentially, you are creating an enumerated(?) type
to be used in a table?

Cool indeed! Actually, a domain definition can be useful for more
than just that: if you define a domain, and then use that domain as a
data type for various columns in various tables, you can change your
schema all at once by changing the definition of the domain. Also, a
domain can carry extra meaning. Look at this schema (using a somewhat
arcane syntax) for keeping track of suppliers, parts and shipments of
quantities of parts from suppliers:

DOMAIN S# CHARACTER (5) PRIMARY
DOMAIN SNAME CHARACTER (40)
DOMAIN P# CHARACTER (5) PRIMARY
DOMAIN PNAME CHARACTER (20)

RELATION S (S#, SNAME)
PRIMARY KEY (S#)
RELATION P (P#, PNAME)
PRIMARY KEY (P#)
RELATION SP (S#, P#, QTY NUMERIC (4))
PRIMARY KEY (S#,P#)

This is simplified from an example in "An Introduction to Database
Systems", by C.J. Date, taken from the 1981 third edition. Note how
the named domains become the default types for columns of the same
name as the domains, while the QTY column in the SP relation has an
explicit data type. Note also the constraints: the "PRIMARY KEY"
statements in the RELATION definitions make uniqueness constraints,
and the word "PRIMARY" in the DOMAIN definitions for S# and P# specify
that these domains are foreign keys, thus demanding referential
integrity from the SP table to the S and P tables. Neat, innit? :-)

Does modern SQL have this stuff? I'm not up-to-date, I'm afraid...

-tih
--
Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"

The datatype employed is defined by domain which also
restricts the values to "YES" or "NO" or "RETIRED" or "DISABLED" or
NULL.

Oh, cool...so, essentially, you are creating an enumerated(?) type
to be used in a table?

Bruce, can you add this onto the TODO list for v6.4? This is
something that we might be able to do now with triggers, no? But, the
CREATE DOMAIN is part of the spec... :)

Added.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Tom I Helbekkmo wrote:

On Sun, Mar 01, 1998 at 03:01:12PM -0500, The Hermit Hacker wrote:

The datatype employed is defined by domain which also
restricts the values to "YES" or "NO" or "RETIRED" or "DISABLED" or
NULL.

Oh, cool...so, essentially, you are creating an enumerated(?) type
to be used in a table?

...

Does modern SQL have this stuff? I'm not up-to-date, I'm afraid...

The only thing I know of like this is the REFERENCES keyword. You can
do the following (Sybase example):

Create a table users where the userid field is an identity
(automatically generates the next number in the sequence during the
insert) unique and not null. Sybase makes you use numeric fields for
identities (I.E. can't use int), but we could do better :)

1> create table users (username varchar(30) not null,
2> userid numeric(20,0) identity unique not null)
3> go

Create a table that stores information based on a given userid.

1> create table usage(userid numeric(20,0) not null references users(userid),
2> login_time datetime not null,
3> logout_time datetime not null)
4> go

The "references" keyword means that an item can be in this table
(usage) iff there is a corresponding entry in the users table. For
example:

1> insert into users (username) values("ocie")
2> select @@identity
3> go
(1 row affected)

-----------------------------------------
1

(1 row affected)

This inserted a user "ocie" and selected the magic variable
@@identity, which is my userid. I can try inserting into usage with
other userids:

1> insert into usage (userid,login_time,logout_time) values (2,getdate(),getdate())
2> go
Msg 546, Level 16, State 1:
Line 1:
Foreign key constraint violation occurred, dbname = 'ociedb', table name =
'usage', constraint name = 'usage_userid_1503344420'.
Command has been aborted.
(0 rows affected)

but it fails because there is no such entry in users. I can also add
several entries under my userid:

1> insert into usage (userid,login_time,logout_time) values (1,getdate(),getdate())
2> go
(1 row affected)
1> insert into usage (userid,login_time,logout_time) values (1,getdate(),getdate())
2> go
(1 row affected)

and retrieve them:

1> select * from usage
2> go
userid login_time logout_time
----------------------- -------------------------- --------------------------
1 Mar 1 1998 5:43PM Mar 1 1998 5:43PM
1 Mar 1 1998 5:43PM Mar 1 1998 5:43PM

(2 rows affected)

I can't delete this user from the users table until all the rows that
reference it have been removed:

1> delete from users where userid=1
2> go
Msg 547, Level 16, State 1:
Line 1:
Dependent foreign key constraint violation in a referential integrity
constraint. dbname = 'ociedb', table name = 'users', constraint name =
'usage_userid_1503344420'.
Command has been aborted.
(0 rows affected)

This can also be set up so that multiple fields in another table
define the reference, and I believe it can also be set up so that
referencees (is that a real word?) are deleted, rather than generating
the above message.

This can of course be done with triggers, but I think that external
key and references are good examples of "code as documentation".

Ocie

Yes!! it is very cool feature in SQL. I used them
very often and it saves hell of time!! The reason
I can do 'Alter domain' to change char(10) to char(30)
and it is propogated in all the tables automatically
whereever domain is used!! Also need to have 'ALTER DOMAIN' very
powerful feature.

al

---Tom I Helbekkmo <tih@Hamartun.Priv.NO> wrote:

On Sun, Mar 01, 1998 at 03:01:12PM -0500, The Hermit Hacker wrote:

The datatype employed is defined by domain which also
restricts the values to "YES" or "NO" or "RETIRED" or "DISABLED"

or

NULL.

Oh, cool...so, essentially, you are creating an enumerated(?) type
to be used in a table?

Cool indeed! Actually, a domain definition can be useful for more
than just that: if you define a domain, and then use that domain as a
data type for various columns in various tables, you can change your
schema all at once by changing the definition of the domain. Also, a
domain can carry extra meaning. Look at this schema (using a somewhat
arcane syntax) for keeping track of suppliers, parts and shipments of
quantities of parts from suppliers:

DOMAIN S# CHARACTER (5) PRIMARY
DOMAIN SNAME CHARACTER (40)
DOMAIN P# CHARACTER (5) PRIMARY
DOMAIN PNAME CHARACTER (20)

RELATION S (S#, SNAME)
PRIMARY KEY (S#)
RELATION P (P#, PNAME)
PRIMARY KEY (P#)
RELATION SP (S#, P#, QTY NUMERIC (4))
PRIMARY KEY (S#,P#)

This is simplified from an example in "An Introduction to Database
Systems", by C.J. Date, taken from the 1981 third edition. Note how
the named domains become the default types for columns of the same
name as the domains, while the QTY column in the SP relation has an
explicit data type. Note also the constraints: the "PRIMARY KEY"
statements in the RELATION definitions make uniqueness constraints,
and the word "PRIMARY" in the DOMAIN definitions for S# and P# specify
that these domains are foreign keys, thus demanding referential
integrity from the SP table to the S and P tables. Neat, innit? :-)

Does modern SQL have this stuff? I'm not up-to-date, I'm afraid...

-tih
--
Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"
--
Official WWW Site: http://www.postgresql.org
Online Docs & FAQ: http://www.postgresql.org/docs
Searchable Lists: http://www.postgresql.org/mhonarc

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Sun, 1 Mar 1998, al dev wrote:

Yes!! it is very cool feature in SQL. I used them
very often and it saves hell of time!! The reason
I can do 'Alter domain' to change char(10) to char(30)
and it is propogated in all the tables automatically
whereever domain is used!! Also need to have 'ALTER DOMAIN' very
powerful feature.

Well, let us get one in at at time :) Bruce has added it to the
TODO list for v6.4...not sure how quickly or easily it can be added
though...

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org