CODEWITHSUNDEEP
sqloracle-databasefunction
Alan Mulligan
Alan Mulligan

Reputation: 1198

Oracle check constraint, call function

I am trying to query a table(wishlist_table) to see how many times a member appears in it.

The business rule I am trying to implement is that a member can have at most five items on there wishlist at any one time.

I have been told to do this as a domain constraint so i have created a function to check how many time a membersId appear in the wishlist table but i get an error when calling the from my check constraint

CREATE TABLE WishlistTest
(
WishlistId NUMERIC(6) NOT NULL PRIMARY KEY,
CONSTRAINT chk_Wishlist CHECK (sw3.wishListUpToFiveItems() >= 0 AND sw3.wishListUpToFiveItems() < 5)
);


CREATE OR REPLACE FUNCTION functionWishListUpToFiveItems
RETURN number IS
total number(1) := 0;
BEGIN
SELECT count(*) into total
FROM Member
WHERE MemberId = 1;

IF total < 5 THEN
    return total;
ELSE RETURN -1;
END IF;
END;

If someone could tell me a better way of going about this or see what I am doing wrong it would be great

Upvotes: 0

Views: 2180

Answers (1)

Justin Cave
Justin Cave

Reputation: 231741

I would guess that your instructor wants you to

  • Add an integer column to the table to store the wish list position
  • Add a constraint that insures that the combination of member and wish list position is unique
  • Add a constraint that limits the wish list position to a value between 1 and 5

For alternate approaches using triggers or materialized views (with a constraint on the materialized view) and probably more discussion about how Oracle should allow some sort of assertion syntax to enforce this sort of constraint, you can look through this askTom thread.

Upvotes: 2

Related Questions