Обсуждение: Redefining an existing Table Schema for Foreign Key Constraint - Question

Hello everyone,

I am new to the forum and fairly new to databases (particularly PostgreSQL).
I have done some searching on the internet and can't really get a hold of an
answer to my question. So here it goes:

I am working on a Customer Purchasing-based project where I must use a
database with existing customer data. The database currently follows the
following schema for two tables:

Creditcards Table
id:integer -- primary key
credit_card_number:varchar(16)
name_on_card:varchar(100)
expiration:date

Customers Table
id:integer -- primary key
first_name:varchar(50)
last_name:varchar(50)
credit_card_number:varchar(16)
address:varchar(200)
email:varchar(50)
password:varchar(20)

Currently, the database contains thousands of records in the Customers and
Creditcards tables. I would like to re-define the Customers table to follow
the following schema:

Customers Table
id:integer -- primary key
first_name:varchar(50)
last_name:varchar(50)
cc_id:integer references Creditcards.id
address:varchar(200)
email:varchar(50)
password:varchar(20)

As you can see, 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.

I would like for the Customers.cc_id to match with a Creditcards.id that has
the same credit_card_number.

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.
--
View this message in context:
http://www.nabble.com/Redefining-an-existing-Table-Schema-for-Foreign-Key-Constraint---Question-tp20294223p20294223.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Brian714 wrote:
> Hello everyone,
>
> I am new to the forum and fairly new to databases (particularly PostgreSQL).
> I have done some searching on the internet and can't really get a hold of an
> answer to my question. So here it goes:
>
> I am working on a Customer Purchasing-based project where I must use a
> database with existing customer data. The database currently follows the
> following schema for two tables:
>
> Creditcards Table
> id:integer -- primary key
> credit_card_number:varchar(16)
> name_on_card:varchar(100)
> expiration:date
>
> Customers Table
> id:integer -- primary key
> first_name:varchar(50)
> last_name:varchar(50)
> credit_card_number:varchar(16)
> address:varchar(200)
> email:varchar(50)
> password:varchar(20)
>
> Currently, the database contains thousands of records in the Customers and
> Creditcards tables. I would like to re-define the Customers table to follow
> the following schema:
>
> Customers Table
> id:integer -- primary key
> first_name:varchar(50)
> last_name:varchar(50)
> cc_id:integer references Creditcards.id
> address:varchar(200)
> email:varchar(50)
> password:varchar(20)
>
> As you can see, 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.
>
> I would like for the Customers.cc_id to match with a Creditcards.id that has
> the same credit_card_number.

This should do it:


ALTER TABLE Customers ADD COLUMN cc_id INTEGER;

UPDATE Customers AS c SET cc_id = Creditcards.id FROM Creditcards WHERE
c.credit_card_number = Creditcards.credit_card_number;


(I'm not sure of the best way to use table aliases in the above statement.)


ALTER TABLE Customers DROP COLUMN credit_card_number;

ALTER TABLE Customers ADD CONSTRAINT fk_credit_card_id FOREIGN KEY
(cc_id) REFERENCES Creditcards (id) ON DELETE CASCADE;

I think those last two can be rolled into one statement but it doesn't
hurt to separate them.

I'm assuming here that Creditcards.id is a SERIAL type. Also, you might
want to read up on foreign keys and decide on the best ON DELETE
scenario for your situation.

> I would like for the data to be consistent.

Indeed ;-)

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

In article <20294223.post@talk.nabble.com>,
Brian714 <bndang@uci.edu> writes:

> Currently, the database contains thousands of records in the Customers and
> Creditcards tables. I would like to re-define the Customers table to follow
> the following schema:

> Customers Table
> id:integer -- primary key
> first_name:varchar(50)
> last_name:varchar(50)
> cc_id:integer references Creditcards.id
> address:varchar(200)
> email:varchar(50)
> password:varchar(20)

> As you can see, 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.

This is the usual 1:n relationship, but I think you got it backwards.
There are two questions to ask:
1. Are there customers with more than one credit card?
2. Are there credit cards owned by more than one customer?

Hallo Harald,

Am 2008-11-03 13:41:52, schrieb Harald Fuchs:
> In article <20294223.post@talk.nabble.com>,
> Brian714 <bndang@uci.edu> writes:
> > Customers Table
> > id:integer -- primary key
> > first_name:varchar(50)
> > last_name:varchar(50)
> > cc_id:integer references Creditcards.id
> > address:varchar(200)
> > email:varchar(50)
> > password:varchar(20)
> This is the usual 1:n relationship, but I think you got it backwards.
> There are two questions to ask:
> 1. Are there customers with more than one credit card?

This could be a problem for the above table...

> 2. Are there credit cards owned by more than one customer?

CreditCards are personaly and sometimes (in France) I need an ID card to
prove, that I am the holder...

So how can one credit card can have more then one owner?

Thanks, Greetings and nice Day/Evening
    Michelle Konzack
    Systemadministrator
    24V Electronic Engineer
    Tamay Dogan Network
    Debian GNU/Linux Consultant


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
+49/177/9351947    50, rue de Soultz         MSN LinuxMichi
+33/6/61925193     67100 Strasbourg/France   IRC #Debian (irc.icq.com)

In article <20081106213736.GB25016@tamay-dogan.net>,
Michelle Konzack <linux4michelle@tamay-dogan.net> writes:

> Hallo Harald,
> Am 2008-11-03 13:41:52, schrieb Harald Fuchs:
>> In article <20294223.post@talk.nabble.com>,
>> Brian714 <bndang@uci.edu> writes:
>> > Customers Table
>> > id:integer -- primary key
>> > first_name:varchar(50)
>> > last_name:varchar(50)
>> > cc_id:integer references Creditcards.id
>> > address:varchar(200)
>> > email:varchar(50)
>> > password:varchar(20)
>> This is the usual 1:n relationship, but I think you got it backwards.
>> There are two questions to ask:
>> 1. Are there customers with more than one credit card?

> This could be a problem for the above table...

>> 2. Are there credit cards owned by more than one customer?

> CreditCards are personaly and sometimes (in France) I need an ID card to
> prove, that I am the holder...

> So how can one credit card can have more then one owner?

That's exactly why I told you "I think you got it backwards".
You need a cust_id column in your CreditCards table, not a cc_id
column in your Customers table.