I'll post the fix here as well.
The problem is in the Foreign Keys that reference this table section of the template.
This problem does not exist for all database schemas. If all of your database releations exist between a primary key of one table and some foreign key in another table, this problem will not creep up. And for the first 4 projects I worked on with CodeSmith this was not a problem.
But in my current project I was scripting my tables for creation in another database and this failed because I have a relation between fields that are not Primary Keys of the Primary Table.
Here is the code from 4.1.2:
--region Foreign Keys that reference this table
<% if (this.ScriptDrop) { %>
<% foreach ( TableKeySchema fk in this.SourceTable.Keys ) { %>
<% if ( fk.ForeignKeyTable != this.SourceTable ) { %>
IF (OBJECT_ID(N'[<%= fk.ForeignKeyTable.Owner %>].[<%= fk.ForeignKeyTable.Name %>]')) IS NOT NULL
ALTER TABLE [<% = fk.PrimaryKeyTable.Owner %>].[<%= fk.ForeignKeyTable.Name %>] ADD
CONSTRAINT [<%= fk.Name %>] FOREIGN KEY
(
<% for ( int i = 0; i < fk.ForeignKeyMemberColumns.Count; i++ ) { %>
[<%= fk.ForeignKeyMemberColumns
.Column.Name %>] <% if ( i > 0 ) { %>,<% } %>
<% } %>
) REFERENCES [<% = fk.PrimaryKeyTable.Owner %>].[<% = fk.PrimaryKeyTable.Name %>] (
<% for ( int i = 0; i < fk.PrimaryKeyTable.PrimaryKey.MemberColumns.Count; i++ ) { %>
[<%= fk.PrimaryKeyTable.PrimaryKey.MemberColumns.Column.Name %>] <% if ( i > 0 ) { %>,<% } %>
<% } %>
)
GO
<% } %>
<% foreach ( ExtendedProperty prop in fk.ExtendedProperties ) { %>
<% if (!prop.Name.StartsWith("CS_")) { %>
EXEC dbo.sp_addextendedproperty N'<%= prop.Name %>', N'<%= prop.Value %>', N'user', N'dbo', N'TABLE', N'<%= SourceTable.Name %>', N'CONSTRAINT', N'<%= fk.Name %>'
<% } %>
<% } %>
<% }
} %>
--endregion
the problem is in bold. Notice it's going after the Primary Keys, rather than what is in the relationship.
Below is the fix:
--region Foreign Keys that reference this table
<% if (this.ScriptDrop) { %>
<% foreach ( TableKeySchema fk in this.SourceTable.Keys ) { %>
<% if ( fk.ForeignKeyTable != this.SourceTable ) { %>
IF (OBJECT_ID(N'[<%= fk.ForeignKeyTable.Owner %>].[<%= fk.ForeignKeyTable.Name %>]')) IS NOT NULL
ALTER TABLE [<% = fk.PrimaryKeyTable.Owner %>].[<%= fk.ForeignKeyTable.Name %>] ADD
CONSTRAINT [<%= fk.Name %>] FOREIGN KEY
(
<% for ( int i = 0; i < fk.ForeignKeyMemberColumns.Count; i++ ) { %>
[<%= fk.ForeignKeyMemberColumns.Column.Name %>] <% if ( i > 0 ) { %>,<% } %>
<% } %>
) REFERENCES [<% = fk.PrimaryKeyTable.Owner %>].[<% = fk.PrimaryKeyTable.Name %>] (
<% for ( int i = 0; i < fk.PrimaryKeyMemberColumns.Count; i++ ) { %>
[<%= fk.PrimaryKeyMemberColumns.Column.Name %>] <% if ( i > 0 ) { %>,<% } %>
<% } %>
)
GO
<% } %>
<% foreach ( ExtendedProperty prop in fk.ExtendedProperties ) { %>
<% if (!prop.Name.StartsWith("CS_")) { %>
EXEC dbo.sp_addextendedproperty N'<%= prop.Name %>', N'<%= prop.Value %>', N'user', N'dbo', N'TABLE', N'<%= SourceTable.Name %>', N'CONSTRAINT', N'<%= fk.Name %>'
<% } %>
<% } %>
<% }
} %>
--endregion
Just in case anyone else runs into this....
-Bernie