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

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