RE: MI-L Auto Increment

[Guy Groves]

Title: Auto Increment

I would not recommend the use of ‘rowid’   Rowid is a function that is applied to a column, one time.  You can update a table column with the ‘rowid’ function. BUT you can NOT create a table where the data type of the column is “ROWID” There is not a restriction in MapInfo databases that specifies that a field is a “primary key”  or that a column must be “not null”, or that a column must be sequential.   There are some things you should consider when using rowid’s as key field, especially if you use the rowid to perform joins between tables.  If you don’t need to use the key to perform ANY joins between tables then go  for it.   To use rowid’s as a key you need the system to auto increment every time an insert is made, MapInfo does not do this.  DBMS like SQL server can.    The foreign tables used to join must also be insert / update with the same key value (rowid) as the primary table.  So DON’T use row id creation in the foreign tables, there is no guarantee, other than row order that the records are in their proper relation.  The foreign table value must be added from the primary table record.   There are also considerations to be made for when a record is deleted.  This leaves a “hole” in the row count, ONLY after the table is “packed”.  If you use the ‘rowid’ function again when updating the table there will be a new sequence number after each deleted record.  This will destroy the key relation with any other table using those key values.  If the table is not packed then the rowed will count the deleted records as if they were there (they actually are, they are only “flagged” as deleted.  (see the problems).   If this is to be a primary key the field should be “not null” it MUST contain a value, MapInfo does not support this.   Any time you use a function (such as rowed) to create keys you must be aware of all the behaviors of that function especially if rows are inserted and deleted.  Then you also need a means of managing any joined tables foreign key value, such as a “trigger’ which is not supported in MapInfo.   Recommendation: I would recommend that you devise an actual key value; long integer (integer in MapInfo) is typically most useful.  You need something that is unique.  (In MapInfo if you want to use rowed as a starting point, that is OK, however you can only use it one time (the first time) to assign rowed values to records that already exist. After that they must be managed manually.  Recalculating them using the rowed function is taboo.)   There is another consideration to make.  On the primary table there should be a “primary key” that is unique.  Only one record will have the key value in the table.  This is not an enforced condition in a MapInfo table, A primary key is “UNIQUE” and “NOT NULL”.   Primary key’s can be strings and composites of columns within most DBM systems, but I would still recommend an integer if possible because of index and performance advantages.   You might consider using a DBMS (SQL Server, Oracle, Informix) as alternative data stores for MapInfo.  I recommend these because you can also store the geometry in the DBMS along with the attributes and you have the advantage of all the DBMS tools to manage the data, establish and enforce conditions, and perform other creative solutions to problems not available directly in MapInfo.     -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 12:34 PM To: [EMAIL PROTECTED] Subject: MI-L Auto Increment             Hey,         I am looking for an easy way to auto increment a column in my table. I would like to populate a column to use as a primary key. Is there an SQL statement I could use to update my table with this data?         Thanks, Jerry Bratton (501) 905-8556 fax (501) 690-5741 cell (501) 905-5741 desk