Topic
1 reply Latest Post - ‏2007-12-11T21:36:44Z by Stan
SystemAdmin
SystemAdmin
1525 Posts
ACCEPTED ANSWER

Pinned topic Table with multiple Primary keys(also constrained as Foreign keys)

‏2007-12-10T17:27:12Z |
I am following a database schema which I located on the internet. It requires some tables to have what they refer to as PF -primary foreign keys. i.e. they act as primary keys on the table while being constrained as foreign keys referring to some other table.
Is this possible in Derby? I am using Netbeans 6 and after creating the basic table, trying to constrain the foreign key results in a strange "Syntax Error" which I can't find.

Thought it might be either a bug or simply not possible.

Here is a sample sql command

create table "Recipes"
(
"recipe_id" INTEGER not null primary key,
“recipe_name” VARCHAR(50),
"recipe_description" VARCHAR(100),

);
create table "Recipes"
(
"recipe_id" INTEGER not null primary key,
“step_number” INTEGER not null primary key,
"instructions" VARCHAR(500),
ADD CONSTRAINT fk_recipe_id_Recipe_steps FOREIGN KEY “recipe_id”
REFERENCES Recipes “recipe_id”

);

Please ignore the formatting errors

Thanks

JG
Updated on 2007-12-11T21:36:44Z at 2007-12-11T21:36:44Z by Stan
  • Stan
    Stan
    267 Posts
    ACCEPTED ANSWER

    Re: Table with multiple Primary keys(also constrained as Foreign keys)

    ‏2007-12-11T21:36:44Z  in response to SystemAdmin
    Hi JG -

    I get this error when I attempt your SQL using IJ:

    ij> create table "Recipes"
    (
    "recipe_id" INTEGER not null primary key,
    "step_number" INTEGER not null primary key,
    "instructions" VARCHAR(500),
    ADD CONSTRAINT fk_recipe_id_Recipe_steps FOREIGN KEY "recipe_id"
    REFERENCES Recipes "recipe_id"
    );
    ERROR 42X01: Syntax error: Encountered "ADD" at line 6, column 1.

    • The ADD keyword is used with 'ALTER TABLE...' - it is not needed when declaring the contstraint as part of 'CREATE TABLE...' and you need parens around the column names:

    ij> create table "Recipes"
    (
    "recipe_id" INTEGER not null primary key,
    "step_number" INTEGER not null primary key,
    "instructions" VARCHAR(500) ,
    CONSTRAINT fk_recipe_id_Recipe_steps
    FOREIGN KEY("recipe_id")
    REFERENCES Recipes("recipe_id"));

    • I use IJ a lot to work out proper syntax. An alternative to that is to set up the debug / test properties listed in the Derby / Cloudscape FAQ:

    http://www.ibm.com/developerworks/db2/library/techarticle/dm-0408br adbury/#cs_setEnv

    Setting ...logSeverity=0 will cause syntax errors to be written to the derby.log file.