CODEWITHSUNDEEP
sqldatabaseforeign-keysh2
l3kov
l3kov

Reputation: 501

Defining a Foreign key constraint in H2 Databases

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

Upvotes: 50

Views: 67288

Answers (3)

Dazed
Dazed

Reputation: 1259

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.

Upvotes: 23

David Brossard
David Brossard

Reputation: 13834

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 
)
  1. 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)
)

Upvotes: 72

danidemi
danidemi

Reputation: 4706

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.

Upvotes: 9

Related Questions