pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Rules, triggers, ??? - What is the best way to enforce data-valid ation tests?
2+ messages / 2 participants
[nested] [flat]

* Rules, triggers, ??? - What is the best way to enforce data-valid ation tests?
@ 2000-01-12 17:52 Greg Youngblood <YoungblG@houstoncellular.com>
  2000-01-27 17:26 ` Re: [SQL] Rules, triggers, ??? - What is the best way to enforce data-valid ation tests?? Jan Wieck <wieck@debis.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Greg Youngblood @ 2000-01-12 17:52 UTC (permalink / raw)
  To: pgsql-sql

I am in the process of creating a large relational database. One of the key
things I need to include in this database is a system to maintain data
integrity across multiple tables.

here's an example:

Table: items
item_id		description		vendor_id		model
cost
stuff1		Stuff			stuff			s-1
14.25

Table: vendors
vendor_id	vendor			contact			phone
stuff     		Stuff Inc.		Mr. Stuff
xxx.xxx.xxxx

Table: order
order_id		item_id			qty
price
1		stuff1			3
19.95

In this example, table "items" would be populated with a list of items with
item_id being a unique key; table "vendors" would be a list of vendors with
vendor_id being a unique key; and, table "order" would be line items from an
order. These are just samples, what i'm trying to illustrate are the
relationships between the tables.

The first relationship would be between "items" and "vendors" and the second
would be between "items" and "order". The same type of relationship exists
in both examples. My goal is to make the database validate any new records
or changes to existing records such that vendor_id in "items" must already
exist in "vendors", and that item_id in "order" must already exist in
"items".

you could not add or change a record to "order" where item_id does not
already exist in "items", and, you could not add or change a record in
"items" where vendor_id does not already exist in "vendors".

There are two ways to handle this. The first way, and the way I've generally
done in the past, is making the data-validation tests part of the program.
This means I could go in to psql and manually enter invalid data. It also
means there is the possibility of the program, due to bug or other problems,
not always enforcing the integrity rules.

The second way is to place these criteria in the database itself, and this
is what I want to do. This is also where I'm at my weakest.

What is the best way to implement this. Best, to me, refers to the simplest,
most direct, and having the least impact on performance. Would it be by
using triggers? Or rules? Or keeping it in the program and not in the
database? Or something else entirely?

In the past I've tried triggers and rules and never did get them to work to
my satisfaction. Either they would work for some tasks, but I couldn't adapt
them to work in more complicated tables, or there were syntax problems and I
never got them to work at all. If anyone can help, and provide some
examples, i would greatly appreciate it.

I currently have postgres 6.5.0 on the production server, and 6.5.1 or 6.5.2
on my development boxes.

Many thanks in advance.
Greg

Gregory S. Youngblood
ext 2164
 
 




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] Rules, triggers, ??? - What is the best way to enforce data-valid ation tests??
  2000-01-12 17:52 Rules, triggers, ??? - What is the best way to enforce data-valid ation tests? Greg Youngblood <YoungblG@houstoncellular.com>
@ 2000-01-27 17:26 ` Jan Wieck <wieck@debis.com>
  0 siblings, 0 replies; 2+ messages in thread

From: wieck@debis.com @ 2000-01-27 17:26 UTC (permalink / raw)
  To: Greg Youngblood <YoungblG@houstoncellular.com>; +Cc: pgsql-sql

> I am in the process of creating a large relational database. One of the key
> things I need to include in this database is a system to maintain data
> integrity across multiple tables.
>
> here's an example:
>
> [...]
>
> In the past I've tried triggers and rules and never did get them to work to
> my satisfaction. Either they would work for some tasks, but I couldn't adapt
> them to work in more complicated tables, or there were syntax problems and I
> never got them to work at all. If anyone can help, and provide some
> examples, i would greatly appreciate it.
>
> I currently have postgres 6.5.0 on the production server, and 6.5.1 or 6.5.2
> on my development boxes.

    Version  7.0 will have support for FOREIGN KEY syntax, that's
    what you're looking for.

    I'm not sure if  ALTER  TABLE  ...  ADD  CONSTRAINT  will  be
    implemented, but at least you can define all the dependencies
    during CREATE TABLE. The support not only includes the checks
    about  key existance as in your example. You can also specify
    how the DB should behave if a referenced key from the primary
    table  is  deleted  or  updated  (for  example let references
    automatically follow, do cascaded deletes  or  simply  reject
    the modification as long as the PK is referenced).

    Get  a  snapshot  of the current development sources from our
    CVS server using  anon-CVS.  Most  of  the  functionality  is
    already there.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-01-27 17:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-12 17:52 Rules, triggers, ??? - What is the best way to enforce data-valid ation tests? Greg Youngblood <YoungblG@houstoncellular.com>
2000-01-27 17:26 ` Re: [SQL] Rules, triggers, ??? - What is the best way to enforce data-valid ation tests?? Jan Wieck <wieck@debis.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox