postgresql multi column unique or check constraint

Question

First a word of pleading. To say I am a newbie to postgresql would be an insult to the newbies! I have been working on this for only a day or so.

I have found several questions on this site, and found references in the postgres 9.1 documentation which allude to this issue, but I am new enough to this that my understanding of the various complex examples is insufficient for me to grasp the concept. At least I think that is the problem...

I have the following table:

CREATE TABLE cc
(
 idx integer NOT NULL DEFAULT nextval('cc_idex_seq'::regclass),
 card_num character(16), -- credit card number
 sec character(4), -- credit card security number from reverse
 card_holder_fname character(20), -- first name of card holder
 card_holder_lname character(20), -- last name of card holder
 card_holder_mi character(1), -- middle initial (if given)
 card_type character(1), -- Visa, Master Card, or Discover
 CONSTRAINT chk_cc_card_type CHECK
  (card_type = ANY (ARRAY['V'::bpchar,'M'::bpchar, 'D'::bpchar]))
)

In an effort to help us combat fraud, we want to ensure that the combination of data for a given table row is the only way that the card in question is ever entered. Since a given card can wind up in the table more than once, but should never do so with different card holder data, multiple entries need to be allowed IF THEY ARE EXACTLY THE SAME! But if a card number appears more than once, with different holder data, it should raise an error.

(Note: This isn't the full table, but it is enough to demonstrate the issue with, I hope...)

I am as yet unable to find a way to do this with sql, and am new enough to postgresql that the functions and triggers elude me.

Any help would be wonderful.

Edit: Per ypercube's suggestion I am ading what I should have said in the beginning.

1) The information is being entered from an employee operated form (web based script of some kind, I will look into this further)

2) The table / constraint should refuse to allow the insert if the conditions are detected. Since a new entry would be the entire transaction involving suspect information the entire transaction should be refused. Obviously the script engine adding the data will have to be modified as well, but I should be able to handle that part. Writing the data to another table as suspect would be allowable since no actual financial transaction would have been allowed.

3) This data is stored in no other table (this was my initial idea to place it in another table and use a foreign key lookup on the card number + data and reject it if not the same...I am pushing the boss to do this but he has to confer with some one about legality...apparently there is an issue of having it in more than one location)

4) And just for the record, the boss knows he needs a pro to do this, I am simply a stop gap! (Hopefully a short stop for a small gap!)

Answer 1

ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK ( column_price > 0 AND column_discount >= 0 AND column_price > discount );