Database Tutorial

Course Tutorial Site

Site Admin

COMMENT Statement

with 2 comments

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. MySQL treats check constraints through the ENUM and SET data types.

COMMENT Statement
OracleMySQLSQL Server

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.';

MySQL

MySQL doesn’t support the COMMENT statement in release 5.1. You can create table and column comments but you must use the CREATE or ALTER statements.

You can create a table with a column and table comment like this:

CREATE TABLE list
( list_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, list_item  VARCHAR(20) COMMENT 'String with or without leading or trailing white space.')
ENGINE = InnoDB COMMENT = 'Sample table of strings.';

You can see the comments by typing the following SHOW command:

SHOW CREATE TABLE list;

SQL Server

Under development …

Written by michaelmclaughlin

October 11th, 2009 at 9:26 am

Posted in

2 Responses to 'COMMENT Statement'

Subscribe to comments with RSS or TrackBack to 'COMMENT Statement'.

  1. Your first example for COMMENT is confusing because it says, “‘A table of small tables, and it mimics the ENUM or SET data type in Oracle’;” And I believe it should say it mimics the ENUM or SET data type in MySQL. Maybe I am misunderstanding something….

    Derek Griggs

    6 Oct 11 at 5:52 pm

  2. Derek, Good catch. It’s fixed.

    michaelmclaughlin

    22 Oct 14 at 3:12 am

Leave a Reply