Path: utzoo!utgpu!jarvis.csri.toronto.edu!cs.utexas.edu!usc!apple!oliveb!amdahl!rtech!squid!robf
From: robf@squid.rtech.com (Robert Fair)
Newsgroups: comp.databases
Subject: Re: Unique code numbers
Message-ID: <4501@rtech.rtech.com>
Date: 17 Jan 90 15:59:35 GMT
References: <1990Jan16.081519.279@cs.eur.nl>
Sender: news@rtech.rtech.com
Reply-To: robf@squid.UUCP (Robert Fair)
Organization: Ingres Corporation, Alameda CA 94501
Lines: 39

>From: reino@cs.eur.nl (Reino de Boer)
writes about a parts/supplier problem:
>I was wondering if there is a relational DBMS that gives me the
>opportunity to indicate that:
>1. SupplierIDs need to be unique
>2. PartIDs need to be unique
>and, most important of all
>3. Frees me from the burden to make up new SupplierIDs and PartIDs.

One easy way of doing this is to use INGRES 6.3 "logical_key" datatypes
which can be automatically set by the system to unique values:

	table_key    - An 8-byte string unique within the table
	object_key   - A 16-byte string unique within the entire DBMS
		       installation.

If the column is declared as 'system_maintained' then INGRES will
places a unique  value in the column when a new  row is inserted, so 
providing an automatic unique value for the column. A typical usage might
be:

	CREATE TABLE supplier (
		supplierID object_key with system_maintained,
		...
	)

	CREATE TABLE supppart (
		supplierID object_key not null not system_maintained,
		...
	)

(suppart.supplierID is not system_maintained since it is derived from
 supplier.supplierID - you don't want different values here! To enforce this
 relationship one could create a Rule on suppart/supplier)


Robert Fair
Technical Support
Ingres Corperation