Database Tutorial

This page shows you how to use the COMMENT statement. The COMMENT statement appears very implementation dependent. Oracle supports it with complete references in the data catalog. SQL Server supports it as an advanced property of tables, and provides custom functions to read and write comments against tables and columns.

The COMMENT statement lets you add a comment to a table or a column in a table. It is very handy for labeling foreign key columns that aren’t constrained by a database constraint because developers can then check the valid list of values. It is also very handy for letting you qualify the list of acceptable values imposed by a CHECK constraint in Oracle.

Oracle treats comments as additional properties of tables, and requires that you add them after you’ve created the table. You find the table-level comments in the USER_TAB_COMMENTS, DBA_TAB_COMMENTS, and ALL_TAB_COLUMNS views. You find the column-level comments in the USER_COL_COMMENTS, DBA_COL_COMMENTS, and ALL_COL_COLUMNS views.

The following syntax lets you add a comment on a table:

COMMENT ON TABLE common_lookup IS 'A table of small tables, and it mimics the MySQL ENUM or SET data type';

The following syntax lets you add a comment on a column of a table. You see an example for mapping the who-audit CREATED_BY column from the COMMON_LOOKUP table back to the SYSTEM_USER table and primary key SYSTEM_USER_ID column that hold the respective primary key. You must qualify the table and column name after the COLUMN keyword. They are separated by a period, which is the component selector (it selects a column from a table, field from a structure, et cetera).

COMMENT ON COLUMN common_lookup.created_by IS 'An unconstrained foreign key references SYSTEM_USER.SYSTEM_USER_ID.';