Stay organized with collections
Save and categorize content based on your preferences.
CONSTRAINT_COLUMN_USAGE view
The CONSTRAINT_COLUMN_USAGE view contains all columns used by
constraints.
For PRIMARY KEY constraints, these are the columns from
the KEY_COLUMN_USAGE view. For FOREIGN KEY constraints, these are the columns
of the referenced tables.
Schema
The INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view has the following schema:
Column Name
Data type
Value
TABLE_CATALOG
STRING
The name of the project that contains the dataset.
TABLE_SCHEMA
STRING
The name of the dataset that contains the table. Also
referred to as the datasetId.
TABLE_NAME
STRING
The name of the table. Also referred to as the
tableId.
COLUMN_NAME
STRING
The column name.
CONSTRAINT_CATALOG
STRING
The constraint project name.
CONSTRAINT_SCHEMA
STRING
The constraint dataset name.
CONSTRAINT_NAME
STRING
The constraint name. It can be the name of the
primary key if the column is used by the primary key or the name of
foreign key if the column is used by a foreign key.
Scope and syntax
Queries against this view must include a dataset qualifier. For queries with a
dataset qualifier, you must have permissions for the dataset. For more
information see
Syntax.
The following table shows the region and resource scopes for this view:
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-25 UTC."],[[["\u003cp\u003eThe \u003ccode\u003eCONSTRAINT_COLUMN_USAGE\u003c/code\u003e view details all columns involved in constraints, including \u003ccode\u003ePRIMARY KEY\u003c/code\u003e columns from \u003ccode\u003eKEY_COLUMN_USAGE\u003c/code\u003e and \u003ccode\u003eFOREIGN KEY\u003c/code\u003e columns from referenced tables.\u003c/p\u003e\n"],["\u003cp\u003eThe schema includes columns like \u003ccode\u003eTABLE_CATALOG\u003c/code\u003e, \u003ccode\u003eTABLE_SCHEMA\u003c/code\u003e, \u003ccode\u003eTABLE_NAME\u003c/code\u003e, \u003ccode\u003eCOLUMN_NAME\u003c/code\u003e, \u003ccode\u003eCONSTRAINT_CATALOG\u003c/code\u003e, \u003ccode\u003eCONSTRAINT_SCHEMA\u003c/code\u003e, and \u003ccode\u003eCONSTRAINT_NAME\u003c/code\u003e, providing comprehensive constraint information.\u003c/p\u003e\n"],["\u003cp\u003eQueries against the \u003ccode\u003eCONSTRAINT_COLUMN_USAGE\u003c/code\u003e view require a dataset qualifier, and users need appropriate permissions for the specified dataset.\u003c/p\u003e\n"],["\u003cp\u003eYou can query the view for a single table's constraints by specifying the table name in the \u003ccode\u003eWHERE\u003c/code\u003e clause or retrieve all constraints within a dataset by omitting it.\u003c/p\u003e\n"],["\u003cp\u003eThe view displays a row for each constraint, or states that there is no data to display if there are no constraints in the specified table or dataset.\u003c/p\u003e\n"]]],[],null,["# CONSTRAINT_COLUMN_USAGE view\n============================\n\nThe `CONSTRAINT_COLUMN_USAGE` view contains all columns used by\n[constraints](/bigquery/docs/primary-foreign-keys).\nFor `PRIMARY KEY` constraints, these are the columns from\nthe `KEY_COLUMN_USAGE` view. For `FOREIGN KEY` constraints, these are the columns\nof the referenced tables.\n\nSchema\n------\n\nThe `INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE` view has the following schema:\n\nScope and syntax\n----------------\n\nQueries against this view must include a dataset qualifier. For queries with a\ndataset qualifier, you must have permissions for the dataset. For more\ninformation see\n[Syntax](/bigquery/docs/information-schema-intro#syntax).\nThe following table shows the region and resource scopes for this view:\n\nReplace the following:\n\n- Optional: \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e: the ID of your Google Cloud project. If not specified, the default project is used.\n\n\u003cbr /\u003e\n\nExamples\n--------\n\nThe following query shows the constraints for a single table in a dataset: \n\n```googlesql\nSELECT *\nFROM PROJECT_ID.DATASET.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE\nWHERE table_name = TABLE;\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e: Optional. The name of your cloud project. If not specified, this command uses the default project.\n- \u003cvar translate=\"no\"\u003eDATASET\u003c/var\u003e: The name of your dataset.\n- \u003cvar translate=\"no\"\u003eTABLE\u003c/var\u003e: The name of the table.\n\nConversely, the following query shows the constraints for all tables in a single dataset. \n\n```googlesql\nSELECT *\nFROM PROJECT_ID.DATASET.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE;\n```\n\nWith existing constraints, the query results are similar to the following: \n\n```\n+-----+---------------------+--------------+------------+-------------+---------------------+-------------------+-------------------------+\n| row | table_catalog | table_schema | table_name | column_name | constraint_catalog | constraint_schema | constraint_name |\n+-----+---------------------+--------------+------------+-------------+---------------------+-------------------+-------------------------+\n| 1 | myConstraintCatalog | myDataset | orders | o_okey | myConstraintCatalog | myDataset | orders.pk$ |\n| 2 | myConstraintCatalog | myDataset | orders | o_okey | myConstraintCatalog | myDataset | lineitem.lineitem_order |\n+-----+---------------------+--------------+------------+-------------+---------------------+-------------------+-------------------------+\n```\n| **Note:** `lineitem.lineitem_order` is the foreign key defined in the `lineitem` table.\n\nIf the table or dataset has no constraints, the query results look like this: \n\n```\n+-----------------------------+\n| There is no data to display |\n+-----------------------------+\n```"]]
