Primary Key / Foreign Key relationship

[Former Member]

Hi Folks,

I have a MASTER_TABLE which has master values . This table has two fields, STATUS & STATUS_TEXT, Key field is STATUS.

This is a check table for another table INSTANCE_TABLE which also has field STATUS; this is not a key field though.

The requirement is: If I attempt to delete a STATUS value from MASTER_TABLE, it should not allow me if there is any record in INSTANCE_TABLE which has the same STATUS value. Is there a way to establish such a relationship between the two tables?

Thank you for the help!

Sid

[Former Member]

Hi Folks,

I have a MASTER_TABLE which has master values . This table has two fields, STATUS & STATUS_TEXT, Key field is STATUS.

This is a check table for another table INSTANCE_TABLE which also has field STATUS; this is not a key field though.

The requirement is: If I attempt to delete a STATUS value from MASTER_TABLE, it should not allow me if there is any record in INSTANCE_TABLE which has the same STATUS value. Is there a way to establish such a relationship between the two tables?

Thank you for the help!

Sid

[Former Member]

> Total Questions: 5 (4 unresolved)

What have you tried yourself so far? Have you created a maintenance view?

Cheers,

Julius

[Former Member]

I can add code in maintenance screen to check deleted values against INSTANCE_TABLE. I want to know if that is the only way to go or this can be established using table relationship.

I have set STATUS field in INSTANCE_TABLE as a foreign key, with MASTER_TABLE as check table . This helps to restrict only master "STATUS' values to be entered into INSTANCE_TABLE.

Thanks,

Sid

Edited by: Sid on Nov 19, 2009 12:10 AM

[Former Member]

Hi,

I thing this can be established using table relationship.

Thanks,

Suman.

[Former Member]

Hi Friend,

Only through table Maintaince only it can be acheived , by table relatio it can not be .

because as you know the non primary key cannot be be check table field .means

In Mater table STATUS is primary key so you are making it as check table field to Insance table STATUS Field.

But in Instance table is not a primary key so , without primary key you will not be able to do the foriegn key relation to the master table ,so that it will not be check table becs of that they check will not happen for your scenario . to acheieve this you need to either make your STATUS field in Instance table also as primary key and do the foerign key relationship with Master table .

Or else write an event in the table maintainance .

Regards,