3

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.

Gauravsa
  • 6,330
  • 2
  • 21
  • 30
Keith Edwards
  • 33
  • 1
  • 1
  • 3

2 Answers2

2

This is the correct way to do it, after creating the table in the netbeans IDE, select table TEAM and right click Foreign Keys and select execute Command and paste the code below 

Alter Table APP.PLAYERS
Add FOREIGN KEY (TEAMNUM_FK)
References APP.TEAM (TEAMNUM);

Next select table Team again and select refresh

ps: Let's assume APP is your default schema

Bamara Coulibaly
  • 729
  • 1
  • 8
  • 7
2

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.

Brian Agnew
  • 268,207
  • 37
  • 334
  • 440
  • Oh Thanks a lot for that, I have managed to fix it now, however I get a new error: Error code -1, SQL state X0Y45: Foreign key constraint 'TEAMNUM' cannot be added to or enabled on table PLAYERS because one or more foreign keys do not have matching referenced keys. Do you have any idea whats wrong now? Thanks. Line 1, column 1 – Keith Edwards Jan 18 '12 at 23:26