I've been having problems creating a foreign key in Java Db through Netbeans. I'm pretty sure I have to use an SQL command to change an attribute in the PLAYERS table into a foreign key as I can only specify primary keys through the interface. I have tried executing this command:
ALTER TABLE PLAYERS ADD CONSTRAINT TEAMNUM_FK
Foreign Key (TEAMNUM) REFERENCES TEAM (TEAMNUM);
It's supposed to add/alter TEAMNUM in the PLAYERS table to a foreign key related to a primary key in TEAM table so that the TEAMNUM is consistant in both tables but it gives error:
Error code -1, SQL state X0Y44: Constraint 'TEAMNUM_FK' is invalid: there is no unique or primary key constraint on table '"APP"."TEAM"' that matches the number and types of the columns in the foreign key. Line 1, column 1
If anyone could help that would be great. thanks.
The error message is pretty clear:
there is either no primary key defined for the table TEAM, or the PK consists of different columns than just (teamnum) or the datatype of the teamnum column in PLAYERS doesn't match the data type of the column teamnum in the table team.
As you have shown your table definitions, I cannot tell which of three alternatives that the error messages explains is relevant in your case.