Foreign key

[Clif40RD]

Hey there!!!!!.......I'm kind of having a problem with creating a foreign key in the derby database on the netbeans platforms.Can anyone help me please?

[pritaeas]

You'll need to be more specific.

[Clif40RD]

ohkay what exactly im trying to say is that the derby database base in the netbeans platform doesnt support foreign key creation natively.....honestly I cant be more specific than that

[Clif40RD]

basically im asking if theres another way in which I can create foreign key despite the fact that I cant make the relations by simply having the characters of a primary in another table or by setting those certain characters to be that of a foreign key

[Schol-R-LEA]

OK, I've finally got Netbeans running and connecting to a local (embedded) database that I created manually with IJ, so I've got a handle on what you are talking about. As it happens, there is a way to add a foreign key to a table in Netbeans, but it isn't quite intuitive.

What you need to do is create the attribute (that is, the field) you are going to use as a foreign key in the table you are relating, making sure it is the same type as the key of the table it relates to. Then, after you have saved the table, you go to the table's etry under the database, and open the tree node so that it shows the indexes and foreign keys. Right click on foreign keys and select Execute Command. You then would code an ALTER TABLE statement in the Command window to add the FK constraint.

For example, in the database I created, I have a table State with a NUMERIC primary key id, and a twoCHAR field state for the abbreviations of the US states. In another table, Address, I have a NUMERIC key state which I will set a foreign key constraint on:

ALTER TABLE APP.Address
ADD CONSTRAINT address_state_fk
FOREIGN KEY (state) 
REFERENCES State (id)

I then hit 'SQL Execute' to add the constraint.

There may be any easier way to do this, but this way does work.

[Clif40RD]

Well that was certainly a good try, the code makes sense but its still not working let me show you. Sorry I might have uploaded the same image multiple times I weren't too sure

[Clif40RD]

And btw I'd also just like to mention that the problem isn't the fact that I have both of the columns as primary keys as illustrated in the properties because I even tried using one of them as a primary and the other as just an index.....here are the contents of the database tables in case they might be needed.

[Schol-R-LEA]

Just to make sure we're on the same page: you went to the 'Foreign Keys' section for the 'Animal_Cruelty' table, selected 'Execute Command', and entered the following code (or something like it):

ALTER TABLE APP.Animal_Cruelty
ADD CONSTRAINT animal_cruelty_branch_fk
FOREIGN KEY branch_codes
REFERENCES Branch_and_Branch_Code (branch_codes)

and executed it?

[Schol-R-LEA]

This is where you will find the information about the foreign key constraint after it is created.

[Clif40RD]

Yes Thats what I done or something like that atleast

[Schol-R-LEA]

And did the constraint show up under Foreign Keys? It won't be listed with the field itself.

[Clif40RD]

Nope it didn't show up at all.

[Schol-R-LEA]

OK, I am official puzzled. Have you looked to make sure it wasn't attached to Branch_and_Branch_Code instead of Animal_Cruelty?

[Clif40RD]

meaning what exactly?

[Schol-R-LEA]

What I mean is, it is possible you reversed the two tables, with the result that it related Branch_and_Branch_Code to the field in Animal_Cruelty rather than the way you wanted it, like so:

ALTER TABLE APP.Branch_and_Branch_Code
ADD CONSTRAINT animal_cruelty_branch_fk
FOREIGN KEY branch_codes
REFERENCES Animal_Cruelty (branch_codes)

It would be an easy mistake to make, if you weren't clear on the syntax. I seem to recall doing something like that once when I was first learning SQL, in fact.

If you look under the Foreign Keys section for Branch_and_Branch_Code, does anything show up?

[Clif40RD]

Nothing is showing up in any of the foreign key folders.

[Schol-R-LEA]

I'm not sure what to suggest, then, other than to try executing the ALTER TABLE command again. Sorry.

[Clif40RD]

Its alright thanks I really appreciate you trying........I need help with safely deleting an entity class though because for some reason I'm not using some of my entiity classes when I try to delete them its just making the situation worse.