On Sun, Oct 09, 2005 at 09:32:55PM -0700, Miles Keaton wrote:
> 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 EXCE
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;
EN
e: +64 6 3546038
Email: [EMAIL PROTECTED]
- Original Message -
From: "Miles Keaton" <[EMAIL PROTECTED]>
To:
Sent: Monday, October 10, 2005 4:25 PM
Subject: Re: [GENERAL] brain-teaser with CONSTRAINT - any SQL experts?
> > I would create a multi-column unique index on t
> I would create a multi-column unique index on the table. This should solve
> the problem mentioned although you may still have an integrity issue if a
> "book" name is mistyped.
Hm?
This sounds promising, except it's the exact opposite of what I need.
Is this what you meant?
CREATE TABLE line
Uwe said:
> how about using 2 tables with according unique/primary key constraints and a
> view to actually access the data (mixing the 2 tables into one) ?
Oliver said:
> Create a separate table with the two columns name and isbn which are
> that table's primary key; on the main table, create a f
on 10/8/05 11:36 PM, [EMAIL PROTECTED] purportedly said:
> 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 o
On Sat, 2005-10-08 at 22:36 -0700, Miles Keaton wrote:
> ... 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.
Create
how about using 2 tables with according unique/primary key constraints and a
view to actually access the data (mixing the 2 tables into one) ?
On Saturday 08 October 2005 22:36, Miles Keaton wrote:
> I'm stuck on a brain-teaser with CONSTRAINT:
>
> Imagine a table like "lineitems" in a bookstor
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 o
