Creating a foreign key in Java db (Netbeans)

java sql javadb
Keith Edwards picture Keith Edwards · Jan 19, 2012 · Viewed 34.8k times · Source

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.

Answer

a_horse_with_no_name picture a_horse_with_no_name · Jan 19, 2012

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.

Related questions

How to convert java.util.Date to java.sql.Date?

I am trying to use a java.util.Date as input and then creating a query with it - so I need a java.sql.Date. I was surprised to find that it couldn't do the conversion implicitly or explicitly …

Read More
could not extract ResultSet in hibernate

I have a problem with Hibernate. I try to parse to List but It throws an exception: HTTP Status 500 - could not extract ResultSet. When I debug, It fault at line query.list()... My sample code here @Entity @Table(name = "…

Read More
Hibernate show real SQL

if I set <property name="show_sql">true</property> in my hibernate.cfg.xml configuration file in the console I can see the SQL. But it's not real SQL... Can I see the SQL code that …

Read More