Re: [HACKERS] Is "CREATE DOMAIN" in 6.3 ??
От | ocie@paracel.com |
---|---|
Тема | Re: [HACKERS] Is "CREATE DOMAIN" in 6.3 ?? |
Дата | |
Msg-id | 9803020148.AA24490@dolomite.paracel.com обсуждение исходный текст |
Ответ на | Re: [HACKERS] Is "CREATE DOMAIN" in 6.3 ?? (Tom I Helbekkmo <tih@Hamartun.Priv.NO>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Следующее
От: "Thomas G. Lockhart"Дата:
Сообщение: Re: [DOCS] Re: [HACKERS] Re: [QUESTIONS] varchar vs text