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

Solution 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)
)

Solution 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.

Solution 3

I would improve on @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)
)

When you define the FOREIGN KEY in this case you can omit to reference explicitly the TOURISTINFO_ID column because H2 knows what column is the primary key in PLANETICKETS.

Comments

[Vithursa Mahendrarajah]

yes, it works. But can we view the schema as well in h2 database?

[David Brossard]

You can export the schema in H2 using SCRIPT TO. See this other Q&A: stackoverflow.com/questions/3256694/…

[tObi]

not sure why you were downvoted to 0. This is exactly what I was looking for. This way I can created a named foreign key in a syntax valid in mysql, mariadb and h2

[Noman_ibrahim]

I do not know why you are downvoted. Actually I saw from my experiences the same problem has different solutions and works differently for different people. For me, other solution did not work but yours is working. I do not know, because I remember I created a foreign key in the H2 database before as the prescribed way like the above solution. But this time it was not working for me. I just came to the end and saw your solution. And it works just by following the " -- no need for touristinfo(TOURISTINFO_ID)". Thank you.