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.

Colombi

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

Damal

Version 2.2.224 doesn't have the constraints table. – Deracinate

@Deracinate If that changed, please post a new answer. – Damal

Answer 2

Something like this will do

select
   KCU.TABLE_SCHEMA,
   KCU.TABLE_NAME,
   KCU.CONSTRAINT_NAME,
   KCU.COLUMN_NAME,
   KCU.ORDINAL_POSITION,
   TC.CONSTRAINT_TYPE
from
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU inner join 
      INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on
      KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME

where TC.CONSTRAINT_TYPE will be either PRIMARY KEY or FOREIGN KEY.

Locust

Answer 3

In my case the information_schema.constraints doesn't exist. I had to do it like so:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 

Trichome

Answer 4

The foreign key exists and can be viewed from the information schema with the below query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = 'CONTACT';

Arriviste