In article <pan.2003.03.21.11.28.22.20813@druid.if.uj.edu.pl>, Michal Adamczak:
> hi
> i am learning postgres and made a library model
>
> i dont want a user to be able to borrow 2 items (copies)
> of the same book so made a function to check it (below)
>
> the function works fine but now i would like to add a constraint,
> unfortunatelly i fail
>
> here's my try:
>
> create function bookborrow_check_insert
> (bookborrow.userid%TYPE, bookborrow.bookitem_id%TYPE)
> returns bool as
> 'select NOT $1 in (select userid from bookborrow where userid = $1 and
> bookitem_id = $2 and returned is null)'
> language sql;
>
>=> alter table bookborrow add check(bookborrow_check_insert\
(userid, bookitem_id));
> ERROR: AlterTableAddConstraint: rejected due to CHECK constraint <unnamed>
>
>=> alter table bookborrow add constraint check_insert check(bookborrow_check_insert\
(userid, bookitem_id));
> ERROR: AlterTableAddConstraint: rejected due to CHECK constraint check_insert
>
Is there data in the table?
Maybe try it on an empty copy of the database.