alter table

[danielkessler]

I'm having trouble figuring how to add to a table a column that references another table.

this works:
alter table timesheets add timesheets_pay_period_id NUMBER


this doesn't:
alter table timesheets add timesheets_pay_period_id NUMBER REFERENCES timesheets_pay_periods(timesheets_pay_period_id)

I've moved the NUMBER to the end, but it didn't work either.

thanks in advance

[PAVB]

...a column that references another table

A foreign key perhaps?

If this is the case,
1- Add your column.
2- Create your FK constraint.

[danielkessler]

I'm trying to do it using these directions:
http://www.infogoal.com/sql/sql-add-foreignkey.htm

I'm not making much progress as I've redone the line several different ways with no success.

alter table timesheets add timesheets_pay_period_id foreign key (timesheets_pay_period_id) REFERENCES timesheets_pay_periods(timesheets_pay_period_id)

No real idea how this should look. Am I supposed to create a named foreign key then add it? I've not had to do that before.

At this point, I might was well create another identical table, migrate the data, drop the old table; recreate old table with this one alteration, migrate the data back and delete the tmp table.

daniel

[PAVB]

-- add new column
alter table timesheets add(timesheets_pay_period_id number)
;
-- create FK constraint
alter table timesheets 
   add constraint fk_timesheets_pay_period_id 
   foreign key (timesheets_pay_period_id) 
   references timesheets_pay_periods(timesheets_pay_period_id)
;

Could you please exec code above and post session log?

[danielkessler]

That worked great - thank you.