Can not able to add foreign key in my table using H2 database

Question

I have been trying to add a foreign key to my table studentJobInformation. I have two tables applicationForm which is having a column versityId. I want to make this as my foreign key to this studentJobInformation table. But unfortunately, I could not do that. First image shows the information of the column of applicationForm table and second image shows the studentJobInformation enter image description here then I write this SQL to add Foreign key.

ALTER TABLE studentJobInformation ADD FOREIGN KEY (versityId) REFERENCES applicationForm((versityId)

But my H2 database shows this error.

Syntax error in SQL statement "ALTER TABLE STUDENTJOBINFORMATION ADD FOREIGN KEY (VERSITYID) REFERENCES APPLICATIONFORM(([]VERSITYID) "; expected "identifier"; SQL statement: ALTER TABLE studentJobInformation ADD FOREIGN KEY (versityId) REFERENCES applicationForm((versityId) [42001-196] 42001/42001*

Evgenij Ryazanov · Accepted Answer · 2020-03-29 00:42:59Z

1

You have two opening parentheses before versityId at the end of the command, you need to remove one.

answered Mar 29, 2020 at 0:42

Evgenij Ryazanov

8,3932 gold badges15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Noman_ibrahim Over a year ago

@Evgenjij Thank you for your hard work. That is what happens to me when I work late at night. I have gone through at least 20 times but could not find this error. But unfortunately, It did not work either. I just removed the (versityId) from the end and it works. My SQl is like this now """ ALTER TABLE studentJobInformation ADD FOREIGN KEY (versityId) REFERENCES applicationForm; """". I do not know why this working. Maybe H2 database knows what is the primary key of applicationForm table.

Evgenij Ryazanov Over a year ago

You need to use ALTER TABLE STUDENTJOBINFORMATION ADD FOREIGN KEY (VERSITYID) REFERENCES APPLICATIONFORM(VERSITYID). You can omit (VERSITYID) after REFERENCES APPLICATIONFORM, but in that case a primary key column (APPLICATIONID) will be used, I guess it isn't your intention. Your schema, however, looks suspicious, normally you shouldn't reference secondary keys in referential constraints, but it's another question.

Noman_ibrahim Over a year ago

Yes, you are right. Your last comment is eye-opening for me. I was wrong from the beginning. I need to change my schema and have to restructure my tables.

Collectives™ on Stack Overflow

Can not able to add foreign key in my table using H2 database

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related