• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • 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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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