Subject: not null, unique and foreign keys

From: [email-masked] (Thomas Balzer)
Date: Sun, 21 Aug 1994 18:57:56 +0200 (MET DST)

Hello everybody,

is there a way to tell Postgres that an attribute in a class is unique or
not null? 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?

Thanks,
--- Thomas

| Thomas Balzer | Hans-Thoma-Strasse 17            | D-79664 Wehr         |
| Germany       | Internet: [email-masked] | Phone: +49-7762-1412 |

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "[email-masked]"

   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================

---

From: [email-masked] (Paul M. Aoki)
Date: Mon, 22 Aug 94 10:34:00 -0700

[email-masked] (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: [email-masked] (Jean-Paul Vetterli)
  To: [email-masked]
  Subject: Re: Managing key-value pairs
  
  > From: [email-masked] (Reinhard Dunkel)
  > Message-Id: <[email-masked]>
  > 
  > [...]
  > 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:	[email-masked] 		
  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
  [email-masked]  |  Dept. of EECS, Computer Science Division (#1776) 
                        |  Berkeley, CA 94720-1776