How to see my table column is foreign key or not in H2 database

Question

I have created two tables user and contact. The user is the parent and contact is the child table. I refer userId as the foreign key in contact table. I have gone through the following query.

CREATE TABLE user(
  userId INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(50) NULL,
  address VARCHAR(150) NULL,
  loginName VARCHAR(45) NOT NULL,
  password VARCHAR(50) NOT NULL,
  role INT(1) NOT NULL DEFAULT 2,
  loginStatus INT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY(userId),
);

=====Second Table=========

CREATE TABLE contact(
  contactId INT NOT NULL AUTO_INCREMENT,
  userId INT NULL,
  name VARCHAR(50) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(50) NULL,
  address VARCHAR(150) NULL,
  remark VARCHAR(150) NULL,
  PRIMARY KEY(contactId),
  CONSTRAINT fk_con_userId FOREIGN KEY (userId)
    REFERENCES user (userId)
  ON DELETE CASCADE ON UPDATE NO ACTION
);

And to see the column information I write the following query in H2 database.

show columns from contact;

But my table does not show the userId as the foreign key.

Answer 1

Yeah, the show command you used does not show the foreign keys. The foreign key you created is there, nevertheless.

To check the foreign key is there you can query the INFORMATION_SCHEMA as in:

select * from information_schema.constraints
  where table_name = 'CONTACT'
    and column_list = 'USERID';

This query shows one row that gives you the details of your FK. The column SQL has the value:

ALTER TABLE PUBLIC.CONTACT ADD CONSTRAINT PUBLIC.FK_CON_USERID FOREIGN KEY(USERID) INDEX PUBLIC.FK_CON_USERID_INDEX_6 REFERENCES PUBLIC.USER(USERID) ON DELETE CASCADE NOCHECK