Foreign keys on migration


#1

I have reached a development plateau and now want to integrate some of
the rails niceties that I passed over to get the system online.

I am fooling with migrations. I am using postgresql. I put in my
migration script (at the end of the ‘up’ method, the following raw sql
code…

# set up primary key restratints for PostgreSQL
execute 'ALTER TABLE ONLY referral_notes ADD CONSTRAINT

referral_notes_pkey PRIMARY KEY (id)’
execute ‘ALTER TABLE ONLY placements ADD CONSTRAINT placements_pkey
PRIMARY KEY (id)’
execute ‘ALTER TABLE ONLY case_managers ADD CONSTRAINT
case_managers_pkey PRIMARY KEY (id)’
execute ‘ALTER TABLE ONLY facilities ADD CONSTRAINT facilities_pkey
PRIMARY KEY (id)’
execute ‘ALTER TABLE ONLY clients ADD CONSTRAINT clients_pkey
PRIMARY KEY (id)’
execute ‘ALTER TABLE ONLY users ADD CONSTRAINT users_pkey PRIMARY
KEY (id)’

# set up foreign key restratints for PostgreSQL
execute 'ALTER TABLE ONLY clients ADD CONSTRAINT fk_cp_case_manager

FOREIGN KEY (case_manager_id) REFERENCES case_managers(id)’
execute ‘ALTER TABLE ONLY placements ADD CONSTRAINT fk_cp_client
FOREIGN KEY (client_id) REFERENCES clients(id)’
execute ‘ALTER TABLE ONLY placements ADD CONSTRAINT fk_cp_facility
FOREIGN KEY (facility_id) REFERENCES facilities(id)’
execute ‘ALTER TABLE ONLY placements ADD CONSTRAINT
fk_cp_case_manager FOREIGN KEY (case_manager_id) REFERENCES
case_managers(id)’
execute ‘ALTER TABLE ONLY referral_notes ADD CONSTRAINT
fk_cp_placement FOREIGN KEY (placement_id) REFERENCES placements(id)’

The primary key constraints are indeed all created but the foreign keys
constraints are not created.

Anyone care to toss me a bone here?

Craig


#2

On Sat, 2006-03-25 at 09:37 -0700, Craig W. wrote:

I have reached a development plateau and now want to integrate some of
the rails niceties that I passed over to get the system online.


whoa - this is strange…I dropped the tables and started over, and
completely removed my sql stuff in the migration script…

$ rake environment RAILS_ENV=test migrate
(in /home/craig/svn/th-db/trunk)
NOTICE: CREATE TABLE will create implicit sequence “users_id_seq” for
“serial” column “users.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“users_pkey” for table “users”
NOTICE: CREATE TABLE will create implicit sequence “clients_id_seq” for
“serial” column “clients.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“clients_pkey” for table “clients”
NOTICE: CREATE TABLE will create implicit sequence “facilities_id_seq”
for “serial” column “facilities.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“facilities_pkey” for table “facilities”
NOTICE: CREATE TABLE will create implicit sequence
“case_managers_id_seq” for “serial” column “case_managers.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“case_managers_pkey” for table “case_managers”
NOTICE: CREATE TABLE will create implicit sequence “clients_id_seq” for
“serial” column “clients.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“clients_pkey” for table “clients”
NOTICE: CREATE TABLE will create implicit sequence “facilities_id_seq”
for “serial” column “facilities.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“facilities_pkey” for table “facilities”
NOTICE: CREATE TABLE will create implicit sequence “placements_id_seq”
for “serial” column “placements.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“placements_pkey” for table “placements”
NOTICE: CREATE TABLE will create implicit sequence
“referral_notes_id_seq” for “serial” column “referral_notes.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“referral_notes_pkey” for table “referral_notes”
NOTICE: CREATE TABLE will create implicit sequence “users_id_seq” for
“serial” column “users.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“users_pkey” for table “users”
NOTICE: CREATE TABLE will create implicit sequence
“case_managers_id_seq” for “serial” column “case_managers.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“case_managers_pkey” for table “case_managers”
NOTICE: CREATE TABLE will create implicit sequence “clients_id_seq” for
“serial” column “clients.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“clients_pkey” for table “clients”
NOTICE: CREATE TABLE will create implicit sequence “facilities_id_seq”
for “serial” column “facilities.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“facilities_pkey” for table “facilities”
NOTICE: CREATE TABLE will create implicit sequence “placements_id_seq”
for “serial” column “placements.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“placements_pkey” for table “placements”
NOTICE: CREATE TABLE will create implicit sequence
“referral_notes_id_seq” for “serial” column “referral_notes.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“referral_notes_pkey” for table “referral_notes”
NOTICE: CREATE TABLE will create implicit sequence “users_id_seq” for
“serial” column “users.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“users_pkey” for table “users”

OK - so it creates the PRIMARY KEY constraints automatically for
me…I’m cool with that.

and just to make sure…changing nothing, I run it again…

[craig@lin-workstation trunk]$ rake environment RAILS_ENV=test migrate
(in /home/craig/svn/th-db/trunk)
NOTICE: CREATE TABLE will create implicit sequence “users_id_seq” for
“serial” column “users.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“users_pkey” for table “users”
NOTICE: CREATE TABLE will create implicit sequence “clients_id_seq” for
“serial” column “clients.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“clients_pkey” for table “clients”
NOTICE: CREATE TABLE will create implicit sequence “facilities_id_seq”
for “serial” column “facilities.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“facilities_pkey” for table “facilities”

and it wants to create the same PRIMARY KEY constraints that already
exist.

and if I run the command again, it does the same thing again…

I am confused.

Craig