jump to navigation

Lack of Index and Constraint Comments November 24, 2011

Posted by mwidlake in Architecture, database design, development.
Tags: , , , ,
trackback

Something I’ve just reminded myself of is that under Oracle you cannot add a comment on an index or a constraint. You can only add comments on tables, views, materialized views, columns of those object types and a couple of esoteric things like Operators, Editions and Indextypes.

Here is an example of adding comments to tables and columns:

set pause off feed off
drop table mdw purge;
create table mdw(id number,vc1 varchar2(10));
comment on table mdw is 'Martin Widlake''s simple test table';
comment on column mdw.id is 'simple numeric PK sourced from sequence mdw_seq';
comment on column mdw.vc1 is'allow some random text up to 10 characters';
--
desc user_tab_comments

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 TABLE_TYPE                                                     VARCHAR2(11)
 COMMENTS                                                       VARCHAR2(4000)

--
select * from dba_tab_comments where table_name='MDW'
/
OWNER                          TABLE_NAME                     TABLE_TYPE
------------------------------ ------------------------------ -----------
COMMENTS
----------------------------------------------------------------------------------------------------
MDW                            MDW                            TABLE
Martin Widlake's simple test table

select * from dba_col_comments where table_name='MDW'
order by column_name
/
OWNER                          TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------
COMMENTS
----------------------------------------------------------------------------------------------------
MDW                            MDW                            ID
simple numeric PK sourced from sequence mdw_seq
MDW                            MDW                            VC1
allow some random text up to 10 characters
-- now to add a big comment so need to use the '-' line continuation character in sqlplus
--
comment on table mdw is 'this is my standard test table.-
 As you can see it is a simple table and has only two columns.-
 It will be populated with 42 rows as that is the solution to everything.'
/
select * from dba_tab_comments where table_name='MDW'
OWNER                          TABLE_NAME                     TABLE_TYPE
------------------------------ ------------------------------ -----------
COMMENTS
----------------------------------------------------------------------------------------------------
MDW                            MDW                            TABLE
this is my standard test table.  As you can see it is a simple table and has only two columns.  It w
ill be populated with 42 rows as that is the solution to everything.
--
/

Adding comments on tables, views and columns seems to have dropped out of fashion over the years but I think it is still a very useful feature of oracle and I still do add them (though I am getting a little slack about it myself over the last 3 or 4 years, which I must stop).

Comments are great, you can put 4000 characters of information into the database about each table, view and column. This can be a brief description of the object, a full explanation of what a column is to hold or even a list of typical entries for a column or table.

But you can’t add a comment on indexes or constraints. Why would I want to? Well, constraints and indexes should only be there for a reason and the reason is not always obvious from either the names of the columns or the name of the constraint or index, especially where you have a naming standard that forces you to name indexes and constraints after the columns they reference.

When you design a database, do a schema diagram or an ERD, you label your relationships between entities/tables. It tells you exactly what the relationship is. You might create an index to support a specific method of access or particular business function. You might alter the index in a way not immediately obvious to the casual observer, such as to allow queries that use the index to avoid having to visit the table. All of those things will, of course, be fully documented in the maintained project documentation in the central repository, available and used by all…

If I was able to add comments to constraints and indexes within the database then they would there. You move the system from one platform to the other, they are there. If for any wildly unlikely reason the central documentation lets you down, the information is always there in the database and easy to check. You may not be able to track down the original design documents but you have the database in front of you, so comments in that will persist and be very easy to find.

Lacking the ability to add comments on indexes and constraints, I have to put them at the table level, which I always feel is a kludge. I might actually raise an enhancement request for this, but as Oracle 12 is already nailed down, it will have to wait until Oracle 14. (A little bird told me Larry said there would be no Oracle 13…).

About these ads

Comments»

1. Keiran Raine - November 25, 2011

Hi Martin,

I have to say I’ve always wanted at least the ability to specify that a sequence is ‘linked’ to a table. Other systems use the alternative of the ‘autoincrement’ column which technically handles this (which you request the value for after the insert)…but they don’t provide a simple solution for keys to be used across multiple primary keys.

Regards

mwidlake - November 25, 2011

Hi Kieran,

I know what you mean, it seems to be a constant “want” and I think it is the question I can be sure will come up when I do intro courses on sequences – how do you establish the column to the sequence.

I use a naming convention and a column comment (if I remember!)

2. Michael Garfield Sørensen - November 25, 2011

Hi Martin,

I little funny hack to add a comment on a default value definition:

Comments on Default Values = add remark after default value:

SQL> CREATE TABLE tdcomm(
2 id NUMBER NOT NULL,
3 td VARCHAR2(1) NOT NULL
4 );

Tabel er oprettet.

SQL> ALTER TABLE tdcomm ADD
2 CONSTRAINT tdcomm_pk PRIMARY KEY(id);

Tabel er ændret.

SQL> ALTER TABLE tdcomm MODIFY(
2 td DEFAULT ‘N’ — N or Y
3 );

Tabel er ændret.

SQL> ALTER TABLE tdcomm ADD
2 CONSTRAINT tdcomm_ck_td CHECK(td IN (‘N’,’Y’));

Tabel er ændret.

SQL> SELECT column_name,nullable,data_default,default_length
2 FROM user_tab_columns
3 WHERE table_name=’TDCOMM';

COLUMN_NAME N DATA_DEFAULT DEFAULT_LENGTH
———————- – ——————– ————–
ID N
TD N ‘N’ — N or Y 14

Br, Michael

mwidlake - November 25, 2011

HI Michael, thanks a lot for that!

What’s really odd is I there has just been a discussion about this between oaktable members. Boris Dali highlighted the issue, he raised bug #8546537 about it (I have not checked out the bug notes on metalink yet) and Mark Farnham expanded Boris’s example, which I have stolen, tweaked and show below:

set pagesize 40 linesize 100 pause off
column column_name format a14
column default_data format a40
drop table test_com purge;
create table test_com
(id number,
line_date date default sysdate — comment
, block_date date default sysdate /* comment */
, line_lit varchar(20) default ‘line_lit’ — c
, block_lit varchar(20) default ‘block_lit’ /* comment */
)
/
insert into test_com(id) values (0);

select column_name,data_default from user_tab_columns
where table_name = ‘TEST_COM';

select * from test_com;


— ****Results****

mdw11> @eric
Table dropped.
Table created.
1 row created.
— from user_tab_columns
COLUMN_NAME DATA_DEFAULT
————– —————————————————————————
ID ~
LINE_DATE sysdate — comment
BLOCK_DATE sysdate /* comment */
LINE_LIT ‘line_lit’ — c
BLOCK_LIT ‘block_lit’ /* comment */

–The row
ID LINE_DATE BLOCK_DATE LINE_LIT BLOCK_LIT
———- ——————– ——————– ——————– —————-
0 25-NOV-2011 20:25:58 25-NOV-2011 20:25:58 line_lit block_lit



drop table test_com2 purge;
create table test_com2
(id number,
line_date date default sysdate — comment
, block_date date default sysdate /* comment */
, line_lit varchar(100) default ‘block_lit’–‘;’ drop table test_com';’
, block_lit varchar(100) default ‘block_lit’–; drop table test_com; ‘
)
/
insert into test_com2(id) values (0);

select column_name,data_default from user_tab_columns
where table_name = ‘TEST_COM2′;

select * from test_com2;

select * from test_com;

I noticed that the comment does not get inserted as a default value so it is parsed away, so the second part was my very quick attempt to see if the comment could be set to something that was like a SQL injection. It didn’t work :-)

3. Graham - November 25, 2011

Table comments ARE great. How many people use them? I’d estimate at about 1% of developers / dbas and that’s probably being generous.

mwidlake - November 25, 2011

Well, we should stand firm and reintroduce them! I’m starting the CAT – Comment All Tables(andcolumnsandviews) party.
Meow.

4. Comments on Constraints and Indexes in the Database, a Good Idea? » Eddie Awad's Blog - January 24, 2012

[...] Martin Widlake thinks so: Comments are great, you can put 4000 characters of information into the database about each table, view and column. This can be a brief description of the object, a full explanation of what a column is to hold or even a list of typical entries for a column or table. But you can’t add a comment on indexes or constraints. Why would I want to? Well, constraints and indexes should only be there for a reason and the reason is not always obvious from either the names of the columns or the name of the constraint or index, especially where you have a naming standard that forces you to name indexes and constraints after the columns they reference. [...]

5. Interesting Bug » Eddie Awad's Blog - January 24, 2012

[...] posted a comment on Martin’s blog, that I previously quoted, about a “hack” to add a comment on a [...]

6. Interesting Bug | Oracle Administrators Blog - by Aman Sood - January 27, 2012

[...] Bug Michael posted a comment on Martin’s blog, that I previously quoted, about a “hack” to add a comment on a [...]

7. Comments on Constraints and Indexes in the Database, a Good Idea? | Oracle Administrators Blog - by Aman Sood - January 27, 2012

[...] on Constraints and Indexes in the Database, a Good Idea? Martin Widlake thinks so: Comments are great, you can put 4000 characters of information into the database about [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 161 other followers

%d bloggers like this: