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 ;-)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
