asked on

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)
);

for_yan

should not you have column name after protals

like portalls(p_id) ?

for_yan

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

snajalm

ASKER

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)
);

for_yan

Do you jhave column portal_id in the protals table ?

snajalm

ASKER

Yes, of course!

snajalm

ASKER

And this is the error code!

Error code -1, SQL state 42X01: Syntax error: Encountered "foreign" at line 6, column 27.

ASKER CERTIFIED SOLUTION

for_yan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

for_yan

in the above example the filed istelf is after foreign and in parentheses

for_yan

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

snajalm

ASKER

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!