Re: [HACKERS] Is "CREATE DOMAIN" in 6.3 ??

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