Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

FOREIGN KEY TO FOREIGN KEY

Hi All,

I have 3 tables :

1. TMUNIT
    1. UnitCode
    2. UnitName

2. TMITEM
    1. ItemCode
    2. ItemName
    3. UnitCode
    4. UnitCode1
    5. UnitCode2

3. TMPRICE
    1. ItemCode
    2. UnitCode
    3. UnitCode1
    4. UnitCode2

Is it possible to alter foreign key TMPRICE To TMITEM for UnitCode, UnitCode1, and UnitCode2. Foreign Key to Foreign Key.

Thank you.
0
emi_sastra
Asked:
emi_sastra
  • 2
2 Solutions
 
rpkhareCommented:
If I understand correctly, you want to:
     (1)  Remove Foreign Key from TMPrice
     (2)  Add new Foreign Key to TMItem for columns UnitCode1 and UnitCode 2.

To remove foreign key:
ALTER TABLE TMPrice DROP CONSTRAINT <FOREIGN_KEY_NAME>

Open in new window


To add new foreign key:

ALTER TABLE TmpItem
ADD CONSTRAINT fk_UnitCode
FOREIGN KEY (UnitCode1, UnitCode2)
REFERENCES TMUnit(UnitCode)

Open in new window


And if you want to keep Foreign Key in both tables, simply add a new Foreign Key constraint to TMUnit as shown below.

Foreign Key To Foreign Key what you are saying does not make much sense because, ideally, all Unit Codes in TMPrice definitely exist in TMItem. So just link your TMPrice with TMItem.
0
 
emi_sastraAuthor Commented:
Hi rpkhare,

Why I am thinking of foreign key to foreign key.
Because I want to prevent TMITEM UnitCode being changed after TMPRICE has data.

How could I overcome this ?

Thank you.
0
 
vivekkumarSharmaCommented:
There is not a direct way to prevent the update in TMITEM when some data exists in TMPRICE
for UnitCode, UnitCode1, and UnitCode2.

You require to add instead of trigger on TMITEM, In which check if data exists in TMPRICE, then do not allow to update else go for update.

Alternatively, you can check it in the SP which is going to update entry of TMITEM table.
0
 
emi_sastraAuthor Commented:
Hi All,

Thank you very much for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now