• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Can't get foreign key column inserted in Java DB

Is there anything wrong with this query in terms of injecting a foreign key into this table?!  For some reason Java DB can apply this query with a foreign key!!

CREATE TABLE data
(
data_ID INT not null primary key
        GENERATED ALWAYS AS IDENTITY
        (START WITH 1, INCREMENT BY 1),    
connect_id_FK INT constraint foreign key references portals,
data_source VARCHAR(100),
user_name VARCHAR(50) not null,  
password VARCHAR(50) not null,
status VARCHAR(20)
);
0
snajalm
Asked:
snajalm
  • 6
  • 4
1 Solution
 
for_yanCommented:
should not you have column name after protals

 like portalls(p_id)   ?
0
 
for_yanCommented:
like in this example

http://www.java2s.com/Code/Oracle/Table/Createtablewithforeignkey.htm

SQL>  CREATE TABLE products
  2      (      product_id      numeric(10)     not null,
  3             supplier_id     numeric(10)     not null,
  4             CONSTRAINT fk_supplier
  5               FOREIGN KEY (supplier_id)
  6               REFERENCES supplier(supplier_id)
  7      );

Open in new window

0
 
snajalmAuthor Commented:
This doesn't work too and it's very frustrating as why it shouldn't!  It doesn't like the bold line for some weird reason!

CREATE TABLE data
(
data_ID INT not null primary key
        GENERATED ALWAYS AS IDENTITY
        (START WITH 1, INCREMENT BY 1),    
connect_id_FK foreign key references portals(portal_id),    
data_source VARCHAR(100),
user_name VARCHAR(50) not null,  
password VARCHAR(50) not null,
status VARCHAR(20)
);
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
for_yanCommented:
Do you jhave column portal_id in the protals table ?
0
 
snajalmAuthor Commented:
Yes, of course!
0
 
snajalmAuthor Commented:
And this is the error code!

Error code -1, SQL state 42X01: Syntax error: Encountered "foreign" at line 6, column 27.
0
 
for_yanCommented:
Look they have different syntax here

http://stackoverflow.com/questions/7435454/java-db-why-cant-i-create-this-table-with-these-foreign-key-constraints

private final static String createExpensesTable =
    "CREATE TABLE expenses ("+
    "expenseID INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "+
    "amount INTEGER NOT NULL, "+
    "date VARCHAR(255) NOT NULL, "+
    "categoryID INTEGER, "+
    "userID INTEGER NOT NULL, "+
    "FOREIGN KEY (categoryID) REFERENCES categories (categoryID) ON DELETE SET NULL, "+
    "FOREIGN KEY (userID) REFERENCES users (userID) ON DELETE CASCADE"+
")";

Open in new window

0
 
for_yanCommented:
in the above example the filed istelf is after foreign and  in parentheses
0
 
for_yanCommented:
looks like  they fisrt decalre field and then declare that it is foreign;
forget about on delete etc.
  "categoryID INTEGER, "+
    "userID INTEGER NOT NULL, "+
    "FOREIGN KEY (categoryID) REFERENCES categories (categoryID) , "+
    "FOREIGN KEY (userID) REFERENCES users (userID) "

Open in new window


0
 
snajalmAuthor Commented:
Java DB is retarded this way, it was the strangest way of introducing a foreign key!  This could have been done in one single line!  

Tons of thanks to you for helping me out with this unusual JavaDB foreign key handling!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now