Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Database Trigger to Prevent Identity Columns on Extension Tables - SQL Server

Overview¶

An "Extension Table" is one where the primary key is also a foreign key against another table. If the primary key on an extension table is an IDENTITY column, it causes problems when trying to insert a new record — especially when using Entity Framework. The following database trigger will rollback the creation or altering of a table that creates this situation.

Code¶

if exists (select 2 from sys.triggers where name = 'ExtensionTableSafety')
    drop trigger ExtensionTableSafety on database
go
/*==================================================================================================
    OBJECT: ExtensionTableSafety database trigger
    SOURCE: http://www.jasinskionline.com/TechnicalWiki/Database-Trigger-to-Prevent-Identity-Columns-on-Extension-Tables-SQL-Server.ashx
==================================================================================================*/
create trigger ExtensionTableSafety
on database
for create_table, alter_table
as begin

declare @result int

;with db as (
    select distinct

         SchemaName     = s.name
        ,TableName      = o.name 
        ,ColumnName     = c.name

        ,IsIdentity     = c.is_identity

        ,IsPkey         = case when c.column_id in 
                            (select k.colid from 
                                sysindexes i 
                                inner join sysindexkeys k on i.indid = k.indid and i.id = k.id
                                inner join sysobjects o2 on i.id = o2.id 
                                inner join syscolumns c2 on k.id = c2.id and k.colid = c2.colid
                            where (i.status & 0x800) = 0x800
                                and o2.id = o.object_id) then 1 else 0 end
    from 
        sys.objects o

        inner join sys.columns c 
            on o.object_id = c.object_id


        inner join sys.schemas s
            on o.schema_id = s.schema_id

    where 1=1
        and o.type in ('u','v')

)
,fk as (
    select
         ForeignKey         = fk.name
        ,OnSchema           = ots.name 
        ,OnTable            = OnTable.name
        ,OnColumn           = OnColumn.name

    from 

        sys.foreign_keys fk

        inner join sys.foreign_key_columns fkcols
            on fk.object_id = fkcols.constraint_object_id

        inner join sys.objects OnTable
            on fk.parent_object_id = OnTable.object_id

        inner join sys.schemas ots
            on OnTable.schema_id = ots.schema_id

        inner join sys.columns OnColumn
            on fkcols.parent_column_id = OnColumn.column_id
            and fkcols.parent_object_id = OnColumn.object_id
    )
select 
    @result = count(2)
    
from 
    db 
    inner join fk 
        on db.SchemaName = fk.OnSchema
        and db.TableName = fk.OnTable
        and db.ColumnName = fk.OnColumn
where 1=1
    and db.IsIdentity = 1 
    and db.IsPkey = 1

if @result > 0 begin

    print 'Oops! You''ve got an Extension Table with a primary key defined as an IDENTITY column.'
    print 'An "Extension Table" is defined as one where the primary key is also a foreign key against another table.'
    rollback


end /* if */


end /* trigger */