Naming foreign keys

[cowwoc]

Hi,

I'm having problems naming foreign keys. Here is the SQL command I am
executing:

CREATE TABLE doctor_clinics (doctor_id BIGINT NOT NULL, clinic_id
BIGINT NOT NULL,
PRIMARY KEY (doctor_id, clinic_id),
CONSTRAINT fk_doctor_id FOREIGN KEY(doctor_id) REFERENCES
doctors(id),
CONSTRAINT fk_clinic_id FOREIGN KEY (clinic_id) REFERENCES
clinics(id));

Here is the error I get:

Syntax error in SQL statement "CREATE TABLE QUEUES (ID IDENTITY
PRIMARY KEY, ""NAME"" VARCHAR NOT NULL, CLINIC_ID BIGINT NOT NULL,
CONSTRAINT FOREIGN KEY[*](CLINIC_ID) REFERENCES CLINICS(ID)) ";
expected "., COMMENT, PRIMARY, CHECK, UNIQUE, FOREIGN"; SQL statement:
CREATE TABLE queues (id IDENTITY PRIMARY KEY, `name` VARCHAR NOT NULL,
clinic_id BIGINT NOT NULL,
CONSTRAINT FOREIGN KEY(clinic_id) REFERENCES clinics(id)) [42001-154]

This SQL statement worked fine before I inserted "CONSTRAINT <name>"
before the foreign keys. What am I doing wrong?

Thanks,
Gili

[cowwoc]

My mistake. The SQL command I mentioned on top is not the one that H2
was tripping on (although they look similar). The problem was this
line:

CONSTRAINT FOREIGN KEY(...)

notice the lack of name between CONSTRAINT and FOREIGN KEY.

Now I'm getting a different error:

Constraint "FK_CLINIC_ID" already exists; SQL statement:

CREATE TABLE queues (id IDENTITY PRIMARY KEY, `name` VARCHAR NOT NULL,
clinic_id BIGINT NOT NULL,

CONSTRAINT fk_clinic_id FOREIGN KEY(clinic_id) REFERENCES clinics(id))
[90045-154]

How could the foreign key "fk_clinic_id" already exist in the above
SQL statement?

Thanks,
Gili

[Maaartin]

On Apr 6, 10:18 pm, cowwoc <cow...@bbs.darktech.org> wrote:
> My mistake. The SQL command I mentioned on top is not the one that H2
> was tripping on (although they look similar). The problem was this
> line:
>
> CONSTRAINT FOREIGN KEY(...)
>
> notice the lack of name between CONSTRAINT and FOREIGN KEY.
>
> Now I'm getting a different error:
>
> Constraint "FK_CLINIC_ID" already exists; SQL statement:
> CREATE TABLE queues (id IDENTITY PRIMARY KEY, `name` VARCHAR NOT NULL,
> clinic_id BIGINT NOT NULL,
> CONSTRAINT fk_clinic_id FOREIGN KEY(clinic_id) REFERENCES clinics(id))
> [90045-154]
>
> How could the foreign key "fk_clinic_id" already exist in the above
> SQL statement?

No idea.... but the command works (as given, after creating the table
clinics). Have you tried
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINTS
to find out more?

[Thomas Mueller]

Hi,

> How could the foreign key "fk_clinic_id" already exist in the above
> SQL statement?

Most likely this foreign key existed _before_ running the above statement.

Regards,
Thomas

[cowwoc]

I'm not sure what exactly happened, but when I try to reproduce the
problem now I cannot. As far as I can remember, I explicitly deleted
the database files before running the test each time. Furthermore, I
tried renaming the foreign keys by adding "2" to the name (which
worked) and looking at the resulting table. I didn't see why a
conflict was reported in the first place.

Anyway, I'll let you if I ever run into this problem again. You are
probably right that something was not right in my test environment.

Gili

On Apr 9, 4:15 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote: