Lack of Index and Constraint Comments November 24, 2011
Posted by mwidlake in Architecture, database design, development.Tags: Architecture, data dictionary, design, documentation, sql*plus
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…).
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
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!)
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
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 🙂
Table comments ARE great. How many people use them? I’d estimate at about 1% of developers / dbas and that’s probably being generous.
Well, we should stand firm and reintroduce them! I’m starting the CAT – Comment All Tables(andcolumnsandviews) party.
Meow.
[…] 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. […]
[…] posted a comment on Martin’s blog, that I previously quoted, about a “hack” to add a comment on a […]
[…] Bug Michael posted a comment on Martin’s blog, that I previously quoted, about a “hack” to add a comment on a […]
[…] 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 […]
“http:trimmed-web-link”> Oracle Plql Online Training
Don’t do that again Venkat. Dropping web links to advertise your services onto people’s blogs is called “spam” and is rude. Also, please read up on PL/SQL before you offer any courses on it. The name is PL/SQL not PLql or PL SQL and PL does not stand for “programming logic”, as the most basic web search will inform you. It also appears from your summary on PL/SQL on your web site that you have no idea at all what PL/SQL is used for.