topic Re: Foreign Key addition in Application Development and Automation Discussions

Foreign Key addition

Former Member — Tue, 30 Sep 2008 08:56:41 GMT

I have three tables

Header

Doc No. --key field

Doc Yr --key field

Line Item

Doc No. --key field

Doc Yr --key field

Line Item --key field

Sub line item table

Doc No. --key field

Doc Yr --key field

Line Item --key field

Sub line item --key field

These tables do not have foreign key.

Now these tables have been transfered to production & they contain huge data.

I want to create maintenance view. For this i need to create Foreign key for:

Line item

Doc No. --key field --Foreign Key --Doc No. Of header

Doc Yr --key field --Foreign Key --Doc year. Of header

Line Item --key field

Sub line item table

Doc No. --key field --Foreign Key --Doc No. Of header

Doc Yr --key field --Foreign Key --Doc year. Of header

Line Item --key field --Foreign Key --Line Item Of line item

Sub line item --key field

My question is will this affect my data in the tables in production. Will it ask for conversion of table.

PLZ help!

Re: Foreign Key addition

Former Member — Tue, 30 Sep 2008 09:08:17 GMT

it should not be a problem.Run Database Utility(SE14) before transporting the table.

Thanks,

Re: Foreign Key addition

Former Member — Tue, 30 Sep 2008 09:10:31 GMT

Good

I dont think there should be any problem if you create any Foreign key field in the development server, or neither it will affect the production data.

Thanks

mrutyun^

Re: Foreign Key addition

raskhan_thajudeen — Tue, 30 Sep 2008 09:39:15 GMT

Hi ,

I dont think there will be any issues. You can go ahead and create the foreign keys .

but do the database udjustments using se14 in production.

Regards,

Raskhan T.

Re: Foreign Key addition

Former Member — Tue, 30 Sep 2008 10:13:01 GMT

Why a database adjust is required? Without it can't we do?

Re: Foreign Key addition

Former Member — Tue, 30 Sep 2008 10:19:54 GMT

Hi,

SE14 will make sure there are no inconsistent data in these tables. If you are going to add check tables and foreign keys.

Imagine that there is data where the Header doesn't exist but the line and sub line entries are still present in the corresponding table, which is possible because you didn't have any check tables before, these entries will have to be adjusted.

regards,

Advait.

Re: Foreign Key addition

Former Member — Tue, 30 Sep 2008 10:35:30 GMT

As u said that as check was not there b4 so there might be some data which is present in sub item table but do not exist in header then after running se14 what shall the system do.

Re: Foreign Key addition

Former Member — Tue, 30 Sep 2008 10:39:52 GMT

in that case se14 would end up in error and it would not activate the table.You need to delete the inconsistent entries in the sub key table and then run SE14

Cheers,

Re: Foreign Key addition

raskhan_thajudeen — Tue, 30 Sep 2008 10:44:09 GMT

the concept is when you add a foreign key , u need to specify a check table. then the data that enters into that field will check the value with the check table.and if the entered value is not in the check table , inconsistency is there, hence se14 will throw an error.

Re: Foreign Key addition

Former Member — Tue, 30 Sep 2008 11:15:02 GMT

Yes thats right, if you run SE14, you will run into trouble and if you dont there will be inconsistent entries. Its like a catch 22 situation and might be inconsistencies anyways.

So what I suggest is that you adjust the entries manually or write a program to delete the inconsistent entries.

regards,

Advait

Re: Foreign Key addition

Former Member — Tue, 30 Sep 2008 11:28:55 GMT

Its huge data which at this point i cannot change. So can i just add foreign key in se11 and let me do not adjust the tables then old data will not be adjusted. I guess with that old data shall remain intact but the new data shall be created with keeping the checks in mind.

Is that going to create a problem.