Is there a good way to link tables and sequences?

Question

I'm pretty sure you can't do this like you can with foreign keys but I'd like to document at the database level (even if it's just as a bit of free text somewhere) these relationships.

Is there some way of adding comments to tables or something like that? I've noticed comments in the sql used to create things are often preserved but not sure how you edit these without recreating the whole object or how you view them without dumping ddl.

I'm interested in doing this in sqlserver and oracle. I'm primarily doing edits from the command line/scripts but often look at the databases using SSMS and Sql developer.

The reason I'm interested in this is to let other developers know how to use the database - both future devs + the ones I'm currently working with. We've got 3 applications using the same db and unfortunately its missing primary key and foreign key constraints in a lot of areas, whilst putting those in will go a long way to making it easier to understand, this would also help a lot aswell.

One hacky way I can think of doing it is adding an extra column to each table with the sequence name but I dont really like that - think its a bit misleading.

The first answers to this question have only addressed how to add comments to database objects; If I understand your rambling question correctly, you want to know what is a good way to document which sequences are used for which columns. Like I mentioned in my answer, you can solve this directly in SQL Server and the newest version of Oracle. Someone has suggested an edit to this question to remove the bit about "link tables and sequences" in the title, but that's the crux of the issue here: how to document this relationship. Comments are of course one way to do this. — Colin 't Hart, May 30 '14 at 10:37
If it's 1:1 - say, you have a Sequence dedicated to Customers, why not just call it dbo.CustomerSequence? I don't think it gets any more self-documenting than that. — Aaron Bertrand, May 30 '14 at 13:45
@AaronBertrand yeah that certainly seems like the simplest way for new stuff — JonnyRaa, May 30 '14 at 13:59
@JonnyLeeds shouldn't be too disruptive to rename old sequences either. — Aaron Bertrand, May 30 '14 at 14:41
@AaronBertrand haha! if only! Unfortunately there are 3 applications sat on the database one of which has been forked about 8 times. It's spread across 3 dbms of various versions and we have only one set of tests (for 1 application) which hit the database. At present its 2 selenium tests. Changing things is pretty scary at the moment! — JonnyRaa, May 30 '14 at 15:10
this might help: CREATE TABLE README_FIRST ... add columns timestamp and text field and everyone will stumble over it when viewing the DB. if you have many tables, move it to the top with AAA_README_FIRST or something like that. — , May 30 '14 at 20:24

score 4 · Answer 1 · edited May 23 '17 at 12:40

4

Easy in Oracle:

comment on table mytable is 'This is a table comment';

comment on column mytable.mycolumn is 'This is a column comment';

You can then view the comments using the USER_TAB_COMMENTS and USER_COL_COMMENTS data dictionary views.

This answer covers it for SQL Server.

edited May 23 '17 at 12:40

Community

1

answered May 30 '14 at 09:34

Philᵀᴹ

31,762
10
83
107

hmm looks more hassle than its worth in sql server + in both it seems the comments aren't very discoverable. thanks for the answer though – JonnyRaa May 30 '14 at 09:52

score 4 · Answer 2 · edited May 31 '14 at 07:26

4

For SQL Server, use:

The IDENTITY column property (all versions); or
CREATE SEQUENCE and NEXT VALUE FOR in a column default constraint (2012 onward).

For Oracle, upgrade to 12c and specify <sequence>.NEXTVAL as the default value for the column.

Problem solved!

edited May 31 '14 at 07:26

Paul White

83,961
28
402
634

answered May 30 '14 at 10:21

Colin 't Hart

9,323
15
35
43

And to amen the other answerers, comment your database objects. Comments are a form of documentation, and documentation works best when it's kept close to what it documents, not as some rarely updated document kept somewhere else. – Colin 't Hart May 30 '14 at 10:24
nice - didn't know you can use sequence nextval as default. That's useful. – JonnyRaa May 30 '14 at 10:47
You can do a similar thing in SQL Server. – Aaron Bertrand May 30 '14 at 12:04

score 1 · Answer 3 · answered May 30 '14 at 09:40

1

For MS SQL Server, it appears to be well described here. Personally, as a DBA, I would _NEVER_ let a programmer give me a script without comments on all tables and columns (written in clear English!).

answered May 30 '14 at 09:40

Vérace

29,825
9
70
84

Is there a good way to link tables and sequences?

3 Answers3