[Java DB] tworzenie tabel nie działa

0

Mam kod, który nie działa i wywala błąd.

Kod:

CREATE TABLE test_types
(
id BIGINT NOT NULL
   PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
type_name VARCHAR(100) NOT NULL
);

CREATE TABLE tests
(
id BIGINT NOT NULL
   PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
type_id BIGINT NOT NULL
   FOREIGN KEY REFERENCES test_types,
test_name VARCHAR(100) NOT NULL
);

CREATE TABLE tariffs
(
id BIGINT NOT NULL
   PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
tariff_name VARCHAR(100) NOT NULL
);

CREATE TABLE tariffs_tests_prices
(
tariff_id BIGINT NOT NULL
          FOREIGN KEY REFERENCES tariffs,
test_id BIGINT NOT NULL
        FOREIGN KEY REFERENCES tests,
price FLOAT NOT NULL,

PRIMARY KEY (tariff_id, test_id)
);

CREATE TABLE firms
(
id BIGINT NOT NULL
   PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
tariff_id BIGINT NOT NULL
          FOREIGN KEY REFERENCES tariffs,
shortname VARCHAR(100) NOT NULL,
fullname VARCHAR(600) NOT NULL
);

CREATE TABLE doctors
(
id BIGINT NOT NULL
   PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
firm_id BIGINT NOT NULL
        FOREIGN KEY REFERENCES firms,
doctor_name VARCHAR(100) NOT NULL,
temp SMALLINT NOT NULL
);

CREATE TABLE documents
(
id BIGINT NOT NULL
   PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
document_year SMALLINT NOT NULL,
document_month SMALLINT NOT NULL
);

CREATE TABLE visits
(
id BIGINT NOT NULL
   PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
document_id BIGINT NOT NULL
            FOREIGN KEY REFERENCES documents,
patient_name VARCHAR(100) NOT NULL,
pesel VARCHAR(11) NOT NULL,
doctor_id BIGINT NOT NULL
          FOREIGN KEY REFERENCES doctors,
visit_day SMALLINT NOT NULL -- brak oznaczany przez 0
);

CREATE TABLE visits_tests
(
visit_id BIGINT NOT NULL
         FOREIGN KEY REFERENCES visits,
test_id BIGINT NOT NULL
        FOREIGN KEY REFERENCES tests,

PRIMARY KEY (visit_id, test_id)
);

Błąd:

Error code -1, SQL state 42X01: Błąd składniowy: Encountered "FOREIGN" at line 6, column 4.
Line 8, column 1

Error code -1, SQL state 42X01: Błąd składniowy: Encountered "FOREIGN" at line 4, column 11.
Line 24, column 1

Error code -1, SQL state 42X01: Błąd składniowy: Encountered "FOREIGN" at line 6, column 11.
Line 35, column 1

Error code -1, SQL state 42X01: Błąd składniowy: Encountered "FOREIGN" at line 6, column 9.
Line 45, column 1

Error code -1, SQL state 42X01: Błąd składniowy: Encountered "FOREIGN" at line 6, column 13.
Line 63, column 1

Error code -1, SQL state 42X01: Błąd składniowy: Encountered "FOREIGN" at line 4, column 10.
Line 77, column 1

Execution finished after 0 s, 9 error(s) occurred.

Co jest nie tak z moim kodem?

0

Dodaj klucze obce przez ALTER TABLE, przykład http://weblogs.java.net/blog/pkeegan/archive/2008/05/input_on_a_new.html

0

Wywaliłem "FOREIGN KEY" zostawiając samo "REFERENCES" i też działa. Czy to będzie działało tak samo, jak poprzez "ALTER TABLE"?

1 użytkowników online, w tym zalogowanych: 0, gości: 1