From: | Miles Keaton <mileskeaton(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: brain-teaser with CONSTRAINT - any SQL experts? |
Date: | 2005-10-10 04:32:55 |
Message-ID: | 59b2d39b0510092132i3549461fn46c19ed2f1d3f309@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Solved!
CREATE OR REPLACE FUNCTION non_duplicated_isbn() RETURNS trigger AS $function$
DECLARE
rez RECORD;
BEGIN
SELECT INTO rez * FROM books WHERE isbn=NEW.isbn AND name != NEW.name;
IF FOUND THEN
RAISE EXCEPTION 'isbn % already used for different book name: %',
NEW.isbn, rez.name;
END IF;
RETURN NEW;
END;
$function$ LANGUAGE plpgsql;
CREATE TRIGGER ndi BEFORE INSERT OR UPDATE ON books FOR EACH ROW
EXECUTE PROCEDURE non_duplicated_isbn();
On 10/8/05, Miles Keaton <mileskeaton(at)gmail(dot)com> wrote:
> I'm stuck on a brain-teaser with CONSTRAINT:
>
> Imagine a table like "lineitems" in a bookstore - where you don't need
> an ISBN to be unique because a book will be in buying history more
> than once.
>
> But you DO need to make sure that the ISBN number is ONLY matched to
> one book name - NOT to more than one book name.
>
> This is OK:
> isbn name
> 1234 Red Roses
> 1234 Red Roses
>
> This is OK: (two books can have the same name)
> isbn name
> 1234 Red Roses
> 5555 Red Roses
>
> This is NOT OK: (an isbn must be tied to one book only!)
> isbn name
> 1234 Red Roses
> 1234 Green Glasses
>
>
> I know it's tempting to say, "just link a separate table for the book
> and don't store the book name" but let's just pretend that's not an
> option - because I'm not actually dealing with books : I just made up
> this simplified version of something at work, where we can't change
> the table : both isbn and name MUST be in the table, and what I'm
> trying to do is put a CONSTRAINT on the table definition to protect
> against user error, by making sure that any entered isbn is only tied
> to one book-name in that table.
>
> Thoughts?
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2005-10-10 05:38:20 | Re: brain-teaser with CONSTRAINT - any SQL experts? |
Previous Message | Adam Lawrence | 2005-10-10 04:16:56 | Re: brain-teaser with CONSTRAINT - any SQL experts? |