Thread: Re: [HACKERS] Is "CREATE DOMAIN" in 6.3 ??
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... :)
> > 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)
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"
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