More information

Table Standards

Add Table

This topic reviews how to add a new table to the netFORUM database.

Steps to Add Table

1. Choose a database table name and 3-character column prefix. Adhere to Table Name Conventions.

Run md_create_table

2. Run the database Stored Procedure md_create_table as shown: 

execute dbo.md_create_table
  @mdt_name = '<<tablename>>',
  @mdt_prefix = '<<3-character column prefix>>',
  @grp_code = 'netForumAdmin,netForumUser,AvectraAdmin',
  @createext = 0, -- or 1 if Avectra R&D are adding a new baseline table
  @mdt_desc = '<<friendly table name>>' -- new parameter added in netFORUM 3.0
go
   

md_create_table parameters

  • @mdt_name - the Name of the table to be created.
  • @mdt_prefix - the 3-character column prefix for all columns in the table.
  • @grp_code - Any groups passed in will be granted select, insert, update and delete SQL permissions on the table to be created. In rare cases, you can enter a different parameter value for @grp_code if, in your netFORUM database, you have configured different SQL Groups.
  • @createext - If creating a custom table, enter 0. If you are work for Avectra R&D and you are adding a new baseline table, enter 1 in order to create an Extender Table, unless this is a metadata or admin sort of table that ordinary users will not be interfacing directly.
  • @grp_code_report - The sp will automatically grant SELECT permissions on the table to the designated SQL Group for the Report User which is 99% of the time netForumReport. In rare cases, you can have a different SQL Group for the report user. If so, choose that group here for the parameter. Otherwise, if you are in the 99% of cases who use netForumReport SQL Group, then you do not need to pass the parameter at all.
  • @mdt_desc - The sp will set an extended property description for the table with the value passed in this parameter. This description will then be set as the description in netFORUM which is shown in the query tool and a few other areas. Note: this parameter is only available in netFORUM 3.0 and later

Troubleshooting md_create_table

Q. When I run this I get error invalid table prefix

A. The 3-character column prefix you chose has already been claimed by another table

Q. When running this SP, I get a SQL error.

A. See Troubleshooting section below for more.

Add Columns, Indexes, Relationships, Constraints

3. Once you have created your table, add any additional columns to it.

See Table/Column Name Conventions for recommendations on how to name columns and which data type to choose. If a column should always hold a value, then make it non nullable. A common oversight is to leave a column nullable when that column should always hold a value. Columns must be lowercase; any uppercase letters will produce errors.

Add any necessary indexes. Generally, we recommend that you index any columns that store a foreign key.

Any columns that contain a foreign key, add a relationship back to the primary key in the primary table. See Table/Column Name Conventions for recommendations on how to name foreign key columns.

Add any necessary unique constraints, including composite unique constraints (a unique constraint on the combination of 2 or more columns) where appropriate. Example, if you have a lookup table with a code, you will probably want to add a unique constraint on the code column. If the database runs on a multi-entity system, then you will make the constraint a composite on code + entity key, eg: 

a99_code + a99_entity_key
   

Any unique constraints added to baseline tables by Avectra developers should be on code + entity_key.

Populate Privileges

4. Run the md_privilege_populate_by_table_quick SP to populate privileges and other related metadata for the database table. This SP has a @table_name parameter for the name of the main table; always use the name of the main table, even if the actual database table to which you added one or more columns was the Extender Table: 

execute dbo.md_privilege_populate_by_table_quick @table_name='<<name of main table>>'
go
   

Alternately, you could run this inside the netFORUM iWeb by running the Populate User Privileges process.

In greater detail this SP will:

Conclusion

At this point, your database table is created, the necessary metadata is populated, and next you can go into netFORUM iWeb to continue your work. Most likely you will next run the Object Wizard.

Alter Table

Drop Column

If you drop a column from a table, you need to delete any associated metadata by running md_metadata_cleanup: 

exec dbo.md_metadata_cleanup

Add Column

See also Column#Create Column Button for how to add a new Column within netFORUM

If you add a new column to a database table, after saving the Table, run this SP to populate any necessary metadata in netFORUM: 

execute dbo.md_privilege_populate_by_table_quick '<<name of table>>'
go

Tip: If you've just added a new column and you don't see it as a choice in the Form Designer, Query or in other Toolkit features, ensure that you have run md_privilege_populate_by_table_quick in SQL and then Clear_cache in netFORUM. After those two steps, you should see your new column(s).

Case Study

Troubleshooting

Duplicate Prefix

Q. When running stored procedures md_privilege_populate_by_table_quick or md_table_populate why do I get this error: 

Server: Msg 2627, Level 14, State 2, Procedure md_table_populate, Line 30 
Violation of UNIQUE KEY constraint 'IX_mdt_name'. 
Cannot INSERT duplicate KEY IN object 'md_table'. 
The statement has been terminated.
   

or this error: 

Msg 2627, Level 14, State 1, Procedure md_table_populate, Line 30
Violation of UNIQUE KEY constraint 'IX_mdt_prefix'.
Cannot INSERT duplicate KEY IN object 'dbo.md_table'.
The statement has been terminated.
   

A. In the SP md_table_populate netFORUM is attempting to insert into the table md_table a record for each database table in your database. If one of those database tables has columns with a 3-character column prefix (for example cst, reg, a10, etc.), and that 3-character column prefix has already been claimed by another table in md_table, then the SQL Server will throw an error because of the unique constraint IX_mdt_name on the column md_table.mdt_prefix.

To resolve this, you must remove from the database any "invalid" tables that should not be there. To locate these tables run the md_table_check_dup_prefix stored procedure in SQL Query Analyzer or SQL Server Studio: 

exec dbo.md_table_check_dup_prefix

This SP will return any duplicate tables. Before dropping a table that might contain useful data, you might want to store its results or DTS the table into a different database.

Q. How did these "invalid" tables get there in the first place?

A. 95% of the time, people are creating these tables using back-end SQL to run data extracts, analyze data, etc. Or they were mistakenly left over from data conversion. These tables just need to be cleaned up. A better way would be to have a separate database where these kinds of "sandbox" tables can exist without larding down the true production netFORUM database.

Q. When running md_table_populate we get this error below. I ran dbo.md_table_check_dup_prefix but no results came up. What could be causing this? 

Msg 2627, Level 14, State 1, Procedure md_table_populate, Line 30
Violation of UNIQUE KEY constraint 'IX_mdt_name'. Cannot INSERT duplicate KEY IN object 'dbo.md_table'.
The statement has been terminated.

This error is caused by trying to insert two rows with the identical value in md_table.mdt_name. This column has a unique constraint to prevent this from happening.

First run the SP exec md_table_check_dup_prefix to see if any two tables have the same 3-character column prefix. If you see two or more tables, then resolve the problem as described in other sections on this page.

If you see no results, then run this diagnostic code in SQL (note, this is stripped out of md_table_populate): 

            SELECT DISTINCT tmp_prefix=convert(char(3),name),tmp_name=convert(nvarchar(128),object_name(id) )
INTO #temp
FROM syscolumns 
WHERE 
	substring(name,4,1)='_' 
	AND id IN (SELECT id FROM sysobjects WHERE type='u' AND [name] NOT LIKE '%[_]ext' 
		AND [name] NOT LIKE 'dtproperties%' 
		AND [name] NOT LIKE 'sys%'
		AND [name] NOT LIKE 'MSpub_identity_range%')
 
DELETE #temp where tmp_name in (select mdt_name from md_table)
 
SELECT * FROM #temp
   

Now look at the results. These are the tables that this SP will try to add. If you see the same table twice, it could be because this is a non-netFORUM table that has varying column names that do not fit into the 3-character column prefix paradigm.

Here is an example of a table that would come up twice:

tmp_prefix tmp_name
glx glcodes
sol glcodes

If these four columns are in the glcodes table then you will wind up with this issue:

  • glx_code
  • glacct
  • sol_desc
  • acct_type

The md_table_populate SP "interpreted" that this table will be added with the 3-character column prefix of "glx" (from the "glx_code" column) and then a second time because of "sol" (from "sol_desc").

Resolution: try to clear out any junk tables in your netFORUM database that are not going to be true netFORUM database tables.

Creating Tables that Avoid Duplicate Prefix

Suppose you want to have various "behind the scenes" tables in your netFORUM database, for any number of purposes, but you do NOT want to have these tables loaded into netFORUM metadata through the SP md_table_populate. Moreover, you don't want these tables to stall md_table_populate with errors. How can you do this?

A little background first. The md_table_populate SP throws errors as a way of keeping your database "clean" of cluttering tables that maybe should not be there. This is not such a bad feature, as databases tend to have chronic problem of getting too many garbage tables that people add and then don't really need. After this happens, people are afraid to get rid of them for fear of breaking something, so they stick around forever.

If you want to have extra non-netFORUM databases in your table, and get around the error-checking of md_table_populate, you can. But if you do this, use self-discipline. Don't clutter and weigh down your database with tables you're not going to use, or with tables that will confuse people and wonder if they even belong in the database. That being said, if you need these tables, here is how you can add them.

Here are some ideas:

1. Here is the trick: md_table_populate is looking for any columns where the fourth character is an underscore character. The logic of the SP goes like this: "I see you have a new table and the first four characters of ANY column are xxx_ so I am assuming you want this table in netFORUM." So all you have to do is make sure all the columns in your table do NOT have the underscore character, or at least not the 4th character. What this means is that you cannot simply have a cst_key column in your tables. Change it to cstKey and you're O.K.

In other words, NONE of these tables will get picked up by md_table_populate: 

            -- no underscores, this will work
            
            CREATE TABLE background ( cstKey uniqueidentifier, fullname nvarchar(50) )
 
-- we have underscores, but in the 5th and 9th place, not the 4th place
CREATE TABLE background ( abcd_cst_key uniqueidentifier, fullname nvarchar(50) )
   

These tables WILL get picked up by md_table_populate whether you want them to or not. The second table will actually get added to netFORUM because "doe" has not been claimed yet by any tables (yet, but it might some day, and then you'll have a problem): 

            -- 4th character of the first column is an underscore:
            
            CREATE TABLE background (cst_key uniqueidentifer, balance money)
 
-- 4th character of the second column is an underscore:
CREATE TABLE background (cstkey uniqueidentifer, doe_ray_me char(10))
   

How can you know in advance if md_table_populate will pick up your table?

Run this SQL below. Any results will be tables that md_table_populate will attempt (but might fail) to insert into md_table: 

            SELECT DISTINCT tmp_prefix=convert(char(3),name),tmp_name=convert(nvarchar(128),object_name(id) )
INTO #temp
FROM syscolumns 
WHERE 
	substring(name,4,1)='_' 
	AND id IN (SELECT id FROM sysobjects WHERE type='u' AND [name] NOT LIKE '%[_]ext' 
		AND [name] NOT LIKE 'dtproperties%' 
		AND [name] NOT LIKE 'sys%'
		AND [name] NOT LIKE 'MSpub_identity_range%')
 
DELETE #temp where tmp_name in (select mdt_name from md_table)
 
SELECT * FROM #temp
   

If you are going to add these sorts of tables, you migth want to establish some naming convention to differentiate these "background" tables from the "standard" tables that netFORUM will "know" about through the metadata.

2. You do not have to run md_table_populate. You can add tables manually through iWeb. This is not a recommended solution, however, because someone is likely to run this SP someday and they will run into this problem.

3. Put these tables into a separate database on the same server. This might not be the ideal solution, but at least it keeps the "real" netFORUM database clean of overhead tables.

4. Simply go with the flow and develop these "background" tables with netFORUM-compliant naming conventions by choosing a 3-character column prefix for all columns and using the md_table_create SP to create it. If you are certain that this table will NEVER be relevant to a user within netFORUM, then maybe don't do this as you are going to create overhead by adding needless metadata, especially to the already-large md_privilegePrivilege table.

Overall, the best solution is probably #1 but it requires care in how you create these database tables that you want in your database but not in netFORUM.

Re-naming Table or Column

Suppose you name a table, then populate the privileges and other metadata. If you then need to change the name of the table, or the columns, this can be very difficult to un-do in the metadata. You cannot just rename your table and then re-run the various SPs. Instead you must delete out the metadata. This script will do this. It assumes that you have not done any Toolkit work or ran the Object Wizard. If you have already started developing this this bad table name, this will be even harder to un-do, and this script below is not sufficient. 

declare @TABLE sysname
 
-- enter the name of the mis-named table whose metadata you want to purge
SET @TABLE = 'BadTable' 
DELETE fw_group_column_privilege WHERE gcp_mdt_name = @TABLE
DELETE fw_group_table_privilege WHERE gtp_mdt_name = @TABLE
DELETE md_privilege WHERE prv_table_name = @TABLE
DELETE md_column WHERE mdc_mdt_name = @TABLE
DELETE md_table WHERE mdt_name = @TABLE
   

Re-name Column

As described above, it is not easy to rename a column if you have already done Toolkit work on it.

If you want to rename a column, after you have already done Toolkit work, the best and safest course of action is to re-do your work. Depending on how far you have gone, there are just too many areas that might need to be redone, and it is safest to just start over. It will probably take you longer to re-name everything, and verify that you did it correctly, then to just redo your work.

Your course of action should be:

  1. Rename the column in the database table
  2. Run the SPs described on this page for when you need to add a new column
  3. re-do your work in netFORUM. You might need to look carefully at any Child Forms or eWeb Web Pages that might have referenced this column
  4. run md_metadata_cleanup to wipe out any metadata references to your old column.

Suppose that you have just started, and you haven't gotten too deep into Toolkit development. This script can help you search-and-replace all the references to the "old" column name and replace it with the "new" column name. This script is NOT guaranteed and should be used with care. 

declare @old sysname, @new sysname
SET @old = 'ind_blah' -- previous name of the column
SET @new = 'ind_blah_ext'  -- name of the new column
 
-- run this script in Query Analyzer to "Text" output
-- "copy" text to Clipboard, paste into new window, run it
SELECT 'update ' + mdc_mdt_name + ' set ' + mdc_name + ' = ''' + @new + ''' where ' + mdc_name + ' = ''' + @old + ''''
FROM md_column 
WHERE 
(mdc_name LIKE '%mdc[_]name%'
OR 
mdc_name LIKE '%column[_]name%'
OR
mdc_name LIKE '%control[_]name%'
OR
mdc_name IN ('obc_control_to','obc_control_from')
OR
mdc_name IN ('mlx_column_to', 'mlx_column_from')
)
AND mdc_name NOT IN ('log_mdc_name')