Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!)

share|improve this question
    
How about not allowing multiple entries? If you have a row with a certain card number (and card_num has a UNIQUE constraint), no second row will be allowed with same card number. I mean, what is the purpose of storing almost identical rows (which would differ only in the automatically provided sequence idx)? –  ypercube Oct 25 '13 at 13:50
    
To me your suggestion would be common sense. But unfortunately I am stepping in to the shoes of the previous DBA who was lost to an auto accident last week. The table iun question is actually a transaction record which because of some obscure crap that the owner says he must comply with has to stay put. End result is this small town doesnt have anyone with the skills to fix it, and since I am 'almost' married into the family I got drafted. Upshot is the multiple entries need to be allowed, and HE wants the possibility of the problem solved. This is a post occurance fix. –  Jase Oct 25 '13 at 14:23
    
OK, fair enough. Can you please edit the question with answers to: 1.Do you have any other table where the card_num is either UNIQUE or the PRIMARY KEY? 2.How is this (cc) populated (by procedures that load data from external sources/files)? 3.What exactly do you want to happen when duplicates appear (Deny insert as you say by raising error? Deny insert of only the rows with duplicates but allow the rest to be inserted? Deny insert but put them instead in another, "fraud_suspicious" table?) –  ypercube Oct 25 '13 at 14:36
    
@ypercube Updated as requested. Sorry for the delay I had step out for a while. –  Jase Oct 25 '13 at 18:09
2  
I think you guys should enlist in a knowledgeable PCI person before you go any farther. Hashing credit card numbers isn't necessarily safer. Doing what you're doing probably violates your merchant agreement. –  Travis Campbell Oct 25 '13 at 18:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.