Defining a foreign key constraint in H2 Databases

I am new to coding, so I created the tables on the SQL server, and it worked, so I used the same command in H2 and said that I have syntax problems with the second table, can someone help?

CREATE TABLE TOURISTINFO(
TOURISTINFO_ID INT PRIMARY KEY,
NAME VARCHAR(25) NOT NULL,
NATIONALITY VARCHAR(15) NOT NULL
)

CREATE TABLE PLANETICKETS(
DESTINATION VARCHAR(10) NOT NULL,
TICKETPRICE NUMERIC(8,2) NOT NULL,
TOURISTINFO_ID INT FOREIGN KEY REFERENCES TOURISTINFO
)

Error

Syntax error in SQL statement "CREATE TABLE PLANETICKETS( 
DESTINATION VARCHAR(10) NOT NULL, 
TICKETPRICE NUMERIC(8,2) NOT NULL, 
TOURISTINFO_ID INT FOREIGN[*] KEY REFERENCES TOURISTINFO 
)"; expected "(, FOR, UNSIGNED, NOT, NULL, AS, DEFAULT, GENERATED, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )"; SQL statement:
CREATE TABLE PLANETICKETS( 
DESTINATION VARCHAR(10) NOT NULL, 
TICKETPRICE NUMERIC(8,2) NOT NULL, 
TOURISTINFO_ID INT FOREIGN KEY REFERENCES TOURISTINFO 
) [42001-173] 42001/42001
+22
source share
2 answers

Two-step process

  1. Create table without foreign key
CREATE TABLE PLANETICKETS(
    DESTINATION VARCHAR(10) NOT NULL,
    TICKETPRICE NUMERIC(8,2) NOT NULL,
    TOURISTINFO_ID INT 
)
  1. Add foreign key constraint
 ALTER TABLE PLANETICKETS
    ADD FOREIGN KEY (TOURISTINFO_ID) 
    REFERENCES TOURISTINFO(TOURISTINFO_ID)

One-step process

CREATE TABLE PLANETICKETS(
  DESTINATION VARCHAR(10) NOT NULL,
  TICKETPRICE NUMERIC(8,2) NOT NULL,
  TOURISTINFO_ID INT,
  foreign key (TOURISTINFO_ID) references touristinfo(TOURISTINFO_ID)
)
+38
source

I would like to improve @ David-Brossard's answer:

CREATE TABLE PLANETICKETS(
    DESTINATION VARCHAR(10) NOT NULL,
    TICKETPRICE NUMERIC(8,2) NOT NULL,
    TOURISTINFO_ID INT,
    FOREIGN KEY(TOURISTINFO_ID) REFERENCES TOURISTINFO -- no need for touristinfo(TOURISTINFO_ID)
)

FOREIGN KEY , TOURISTINFO_ID H2 , PLANETICKETS.

-1

Source: https://habr.com/ru/post/1666050/


All Articles