3

I have a table which stores payments and want to ensure if the payment method is a credit card then the card type field should be IN ('Visa', 'MasterCard', 'Discover', 'American Express'), however if the payment method is not a credit card then the card type field should be NULL.

Will a check constraint allow me to use a statement like this:

(method = 'CC' AND cardType IN ('Visa', 'MasterCard', 'Discover', 'American Express'))
OR
(method != 'CC' AND cardType = NULL)

I might be totally off base with this one since constraints should maybe only be used to check 1 field (not sure).

Also if such a statement is allowed would there possibly be an adverse performance hit?

Edit: I plan on eventually creating a cardType field and have cardType in our payments table as a foreign key... this is just something I'm thinking of doing in the meantime

Chris Klepeis
  • 9,783
  • 16
  • 83
  • 149

1 Answers1

3

Why not just foreign key to another table and allow nulls?

Even better would be to ahve a card type code in another table, and store a key to that.

cjk
  • 45,739
  • 9
  • 81
  • 112
  • +1 - definitely normalise the structure with a CardType table to reference. e.g. 50,000 payments with 'American Express', that's 800,000 bytes with current schema, compared to tad over 50,000 bytes for the normalised approach – AdaTheDev Jul 17 '09 at 15:56
  • We plan on going this route. This is an existing structure I'm working with and until I can create the new card type table and update the existing records I was thinking about using the check constraint – Chris Klepeis Jul 17 '09 at 15:58
  • Some times you would prefer constant values rather than another table, such as mapping to enum value in your application. – Shimmy Weitzhandler Dec 05 '09 at 17:21
  • @Shimmy - you can still use azn enum in your application and have a foreign key table for a full descriptive text. This is actually what I do in the main application I maintain. – cjk Dec 07 '09 at 08:19