Defining a Foreign key constraint in H2 Databases

Question

I am new in coding so I made a tables in SQL server and it worked, so i used the same command in H2 and it said I have a syntax problems with the second table, someone can 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 ) 

The error is

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 

Answer 1

Two-step process

1. Create the table without a foreign key

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

2. Add the 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) ) 

Answer 2

I would add one option to @david-brossard's answer:

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

By using a Constaint Name Definition the foreign key is named explicitly, otherwise H2 assigns it a name based on it's own naming scheme e.g. CONSTRAINT_74.

I feel this makes it safer to manage the constraint later by avoiding ambiguity on use of the name and referencing the name directly defined previously e.g.

ALTER TABLE PLANETICKETS DROP CONSTRAINT FK_PLANETICKET_TOURIST; ALTER TABLE PLANETICKETS ADD CONSTRAINT FK_PLANETICKET_TOURIST FOREIGN KEY (TOURISTINFO_ID) REFERENCES TOURISTINFO(TOURISTINFO_ID)  ON DELETE CASCADE; 

I have started doing this as standard, based on my use of Flyway for an installable software product.

In theory the sequence of Flyway migrations should result in constraints (including Foreign Keys) being applied in the same order and therefore H2 should assign the same name in each copy of database. However, the worry point is removed if a direct name is assigned - one which is referenced in previous migration scripts - rather than one deduced from checking the assigned name in a single database instance.