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 On Apr 6, 12:33 pm, cowwoc <[email protected]> wrote: > 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 -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
