Re: defining an existing Table Schema for Foreign Key Constraint - Question

On Sun, Nov 02, 2008 at 02:30:45PM -0800, Brian714 wrote:
> I would like to convert the column that used to be
> "credit_card_number" from the Customers table and turn it into a "cc_id"
> which is an integer that references the column "id" from the table
> Creditcards.
[...]
> Does anyone know of a script that I can use to do this? Am I supposed to use
> Triggers? How can this be done safely. I would like for the data to be
> consistent. Thank you guys in advance.

If you just want to move the data across all you need is a couple of SQL
statements:

  ALTER TABLE customers
    ADD COLUMN cc_id INTEGER REFERENCES creditcards (id);

  UPDATE customers c SET cc_id = d.id
    FROM creditcards d
    WHERE c.credit_card_number = d.credit_card_number;

The first creates the new "cc_id" column in the customers table and the
second moves the data across.  For this to be valid, you really need
to make sure that there is only one "id" number for each credit card
number, the following is a standard way of doing this:

  SELECT credit_card_number, COUNT(*)
  FROM creditcards
  GROUP BY credit_card_number
  HAVING COUNT(*) > 1;

If you already have a UNIQUE constraint on the "credit_card_number"
column then this will be being enforced by the database already and
the check is redundant.  Another check would be that all the customers
credit cards are already in the "creditcards" table:

  SELECT c.*
  FROM customers c
    LEFT JOIN creditcards d ON c.credit_card_number = d.credit_card_number
  WHERE c.credit_card_number IS NOT NULL
    AND d.credit_card_number IS NULL;

I.e. give me all the customers where they have a credit card number yet
we can't find a matching entry.


On a slightly tangential note, why not use the "credit_card_number"
as the primary key in the creditcards table?  It looks like the
perfect example of a "natural key" and you wouldn't have to change the
"customers" table at all, except maybe to let the database check that
everything matches automatically:

  ALTER TABLE creditcards
    ADD CONSTRAINT creditcards_ccnum_uniq
      UNIQUE (credit_card_number);

  ALTER TABLE customers
    ADD FOREIGN KEY (credit_card_number)
      REFERENCES creditcards (credit_card_number);

After you've made sure everything still works, you may want to drop
the existing "id" out of the creditcards table and upgrade the unique
constraint to a full primary key:

  ALTER TABLE creditcards
    DROP CONSTRAINT creditcards_pkey,
    DROP CONSTRAINT creditcards_ccnum_uniq,
    ADD PRIMARY KEY (credit_card_number),
    DROP COLUMN id;

I'm a strong believer in natural keys at the moment, but it's somewhat
a religious battle!  A search on natural keys or surrogate keys should
give a reasonable view of the battle field.  Which way you go is a
design decision with trade-offs either way, but as you said you were
somewhat new to databases I thought a couple of examples could be
useful---I also get to think I've done something productive today!

Hum, I seem to have completely forgotten about triggers.  They are
useful if you want to automatically keep the customers table up-to-date
while maintaining the existing columns in the table.  Although, in this
case, it may be easier to use a "view" to present the new table designs
in a compatible way to how they were before, if you've got control over
the code that accesses these tables that may not even be needed.

Hope that helps!


  Sam