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.