com.google.cloud.spanner.jdbc.DatabaseMetaData_GetImportedKeys.sql Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of google-cloud-spanner-jdbc Show documentation
JDBC driver for Google Cloud Spanner.
There is a newer version: 2.25.2
Show newest version
/*
 * Copyright 2019 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

SELECT *
FROM (
	SELECT PARENT.TABLE_CATALOG AS PKTABLE_CAT, PARENT.TABLE_SCHEMA AS PKTABLE_SCHEM,
	       PARENT.TABLE_NAME AS PKTABLE_NAME, PARENT_INDEX_COLUMNS.COLUMN_NAME AS PKCOLUMN_NAME,
	       CHILD.TABLE_CATALOG AS FKTABLE_CAT, CHILD.TABLE_SCHEMA AS FKTABLE_SCHEM,
	       CHILD.TABLE_NAME AS FKTABLE_NAME, PARENT_INDEX_COLUMNS.COLUMN_NAME AS FKCOLUMN_NAME,
	       PARENT_INDEX_COLUMNS.ORDINAL_POSITION AS KEY_SEQ,
	       1 AS UPDATE_RULE, -- 1 = importedKeyRestrict
	       CASE WHEN CHILD.ON_DELETE_ACTION='CASCADE' THEN 0 ELSE 1 END AS DELETE_RULE, -- 0 = cascade
	       NULL AS FK_NAME, 'PRIMARY_KEY' AS PK_NAME,
	       7 AS DEFERRABILITY -- 7 = importedKeyNotDeferrable
	FROM INFORMATION_SCHEMA.TABLES PARENT
	INNER JOIN INFORMATION_SCHEMA.TABLES CHILD ON
	           CHILD.TABLE_CATALOG=PARENT.TABLE_CATALOG
	       AND CHILD.TABLE_SCHEMA=PARENT.TABLE_SCHEMA
	       AND CHILD.PARENT_TABLE_NAME=PARENT.TABLE_NAME
	INNER JOIN INFORMATION_SCHEMA.INDEX_COLUMNS PARENT_INDEX_COLUMNS ON
	           PARENT_INDEX_COLUMNS.TABLE_CATALOG=PARENT.TABLE_CATALOG
	       AND PARENT_INDEX_COLUMNS.TABLE_SCHEMA=PARENT.TABLE_SCHEMA
	       AND PARENT_INDEX_COLUMNS.TABLE_NAME=PARENT.TABLE_NAME
	       AND PARENT_INDEX_COLUMNS.INDEX_NAME='PRIMARY_KEY'
	
	UNION ALL
	
	SELECT PARENT.TABLE_CATALOG AS PKTABLE_CAT, PARENT.TABLE_SCHEMA AS PKTABLE_SCHEM, PARENT.TABLE_NAME AS PKTABLE_NAME,
	       PARENT.COLUMN_NAME AS PKCOLUMN_NAME, CHILD.TABLE_CATALOG AS FKTABLE_CAT, CHILD.TABLE_SCHEMA AS FKTABLE_SCHEM,
	       CHILD.TABLE_NAME AS FKTABLE_NAME, CHILD.COLUMN_NAME AS FKCOLUMN_NAME,
	       CHILD.ORDINAL_POSITION AS KEY_SEQ,
	       1 AS UPDATE_RULE, -- 1 = importedKeyRestrict
	       1 AS DELETE_RULE, -- 1 = importedKeyRestrict
	       CONSTRAINTS.CONSTRAINT_NAME AS FK_NAME, CONSTRAINTS.UNIQUE_CONSTRAINT_NAME AS PK_NAME,
	       7 AS DEFERRABILITY -- 7 = importedKeyNotDeferrable
	FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS CONSTRAINTS
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CHILD  ON CONSTRAINTS.CONSTRAINT_CATALOG=CHILD.CONSTRAINT_CATALOG AND CONSTRAINTS.CONSTRAINT_SCHEMA= CHILD.CONSTRAINT_SCHEMA AND CONSTRAINTS.CONSTRAINT_NAME= CHILD.CONSTRAINT_NAME
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PARENT ON CONSTRAINTS.UNIQUE_CONSTRAINT_CATALOG=PARENT.CONSTRAINT_CATALOG AND CONSTRAINTS.UNIQUE_CONSTRAINT_SCHEMA=PARENT.CONSTRAINT_SCHEMA AND CONSTRAINTS.UNIQUE_CONSTRAINT_NAME=PARENT.CONSTRAINT_NAME AND PARENT.ORDINAL_POSITION=CHILD.POSITION_IN_UNIQUE_CONSTRAINT
) IMPORTED_KEYS
WHERE UPPER(FKTABLE_CAT) LIKE ?
  AND UPPER(FKTABLE_SCHEM) LIKE ?
  AND UPPER(FKTABLE_NAME) LIKE ?
ORDER BY PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, KEY_SEQ