Delivery-Date: Fri, 06 May 94 14:10:30 -0700
Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199405061700.KAA09966@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Message-Id: <199405061700.KAA23657@faerie.CS.Berkeley.EDU>
From: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
To: tjw00@spg.amdahl.com (Tom Wye)
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: how to define unique keys 
Reply-To: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
In-reply-to: Your message of Thu, 5 May 94 21:56:51 PDT 
	     <9405060456.AA00988@petal.spg.amdahl.com> 
Date: Fri, 06 May 94 10:00:03 -0700
Resent-To: postgres-redist@postgres.Berkeley.EDU
Resent-Date: Fri, 06 May 94 10:00:05 -0700
Resent-XMts: smtp

tjw00@spg.amdahl.com (Tom Wye) writes:
> I have a question about defining a unique key on a class.
> It appears that the KEY operand on the create class command is not
> implemented. Is this true?

yup.

> how does one create a class with a unique index?

From: jpv@gavdos.pr.net.CH (Jean-Paul Vetterli)
To: postgres@postgres.Berkeley.EDU
Subject: Re: Managing key-value pairs
Resent-Date: Tue, 23 Nov 93 08:11:32 -0800

> From: dunkel@spin.chem.utah.edu (Reinhard Dunkel)
> Message-Id: <9311220311.AA21809@spin.chem.utah.edu>
> 
> [...]
> I am trying to use Postgres 4.1 (RS/6000-370, AIX 3.2.4) to manage
> key-value pairs for a graphical user interface. I am embarrassed to
> admit that I have problems to make this easiest of all database
> applications work smoothly. I would greatly appreciate any help on the
> following problems:
> 
> PROBLEM 1: I have to enforce that my "param" attribute values (keys)
> are unique in a database relation.  However, the command "create foo
> (param=char16, value=text) key (param)\g" is documented in the
> reference manual but the message "WARN:Nov 21 18:10:13:RelationCreate:
> KEY not yet supported" sounds discouraging. So before writing a
> key-value pair I query the database to see if the key is known and
> then either replace or append the tupel.  This approach takes two
> database accesses (one second overhead with libpq each). Is there a
> better way to do this?
> [...]

One approach to enforce uniqueness is to use rules. 
Example: 

---- cut here ----
#  this is a spog script: %spog -f <thisfile>
#  it is idempotent 
#
#  create key-value table:
create kv (k = char16,\
	v = text)
#
#  uniqueness *and* integrity:
define rule kv_r is \
	on append to kv \
	where kv.k = new.k or new.k ISNULL \
	do instead nothing
#
#  data:
append kv (k = "K1", v = "K1's value")
append kv (k = "K2", v = "K2's value")
append kv (k = "K3", v = "K3's value")
append kv (k = "K4", v = "K4's value")
append kv (k = "K5", v = "etc.")
---- cut here ----

I hope this can help.


Jean-Paul Vetterli 		
Route des Pommiers 19		email:	jpv@gavdos.pr.net.CH 		
CH-1723 Marly			phone:	++77 34 38 27 		
Switzerland			fax:	++37 46 53 09 
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  aoki@postgres.Berkeley.EDU
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki

===============================================================================
    To add/remove yourself from the POSTGRES mailing list: send mail with 
    the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"

    If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
    a human will deal with it.  DO NOT post to the "postgres" mailing list.
===============================================================================