agora inbox for postgres@postgres.berkeley.edu
help / color / mirror / Atom feedFrom: Paul M. Aoki <aoki@cs.berkeley.edu>
To: Thomas Balzer <tbalzer@tjb.freinet.de>
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: not null, unique and foreign keys
Date: Mon, 22 Aug 94 10:34:00 -0700
Message-ID: <199408221734.KAA19276@faerie.CS.Berkeley.EDU> (raw)
In-Reply-To: <m0qcGDU-0001WhC@tjb.freinet.de>
tbalzer@tjb.freinet.de (Thomas Balzer) writes:
> is there a way to tell Postgres that an attribute in a class is
> unique or not null?
keys don't work. this may help:
Date: Mon, 22 Nov 93 09:35:31 +0100
From: jpv@gavdos.pr.net.CH (Jean-Paul Vetterli)
To: postgres@postgres.Berkeley.EDU
Subject: Re: Managing key-value pairs
> 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
> How to define a foreign key? I can't find anything in the
> documentation of Postgres 4.2. I think they used foreign keys for
> the system tables, aren't they?
nope.
--
Paul M. Aoki | University of California at Berkeley
aoki@CS.Berkeley.EDU | Dept. of EECS, Computer Science Division (#1776)
| Berkeley, CA 94720-1776
==============================================================================
To add/remove yourself to/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.
==============================================================================
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: postgres@postgres.berkeley.edu
Cc: aoki@cs.berkeley.edu, tbalzer@tjb.freinet.de
Subject: Re: not null, unique and foreign keys
In-Reply-To: <199408221734.KAA19276@faerie.CS.Berkeley.EDU>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox