StrataFrame Forum

Keys in SQL Server

http://forum.strataframe.net/Topic2864.aspx

By Flavelle Ballem - 9/17/2006

In SQL Server, a Unique Key is identified as a Key and a Constraint, both of which have the same name. In the DDT, I don't see an option for Unique Key. How is this implemented in DDT?

Thanks,

Flavelle

By Trent L. Taylor - 9/18/2006

I don't understand the issue.  If you create a new index you can define whether it is unique or not.  Create a new key and refer to the documentation (DDT CHM File):

Tables -> Indexes -> Index Properties

By Flavelle Ballem - 9/19/2006

Trent:

If I edit a table directly in SQL Server and want to add an index or key, there are four options:

1. Primary Key - straight forward, no issues.

2. Unique Index - straight forward, no issues.

3. Non-Unique Index - straight forward, no issues.

4. Unique Key - this is the option that I can't figure out how to do. This is a secondary key that must be unique - enforced through a constraint. In addition, an index is created.

By Steve L. Taylor - 9/20/2006

Hello Flavelle,

4. Unique Key - this is the option that I can't figure out how to do. This is a secondary key that must be unique - enforced through a constraint. In addition, an index is created.

If you are trying to create a column that is unique in addition to the primary key it should work as described in the DDT help.  It is simply a constraint; hence, SQL will throw an exception and you would have to programmatically deal with it in code.

Typically we avoid duplicate records by creating a function within the BO and include the verification in the CheckRulesOnCurrentRow method.

The following code should help.

Steve

---------------------------------------------------------------

For example,

'-- Verify User Id is unique
If UserLoginNameDuplicatesExists(Me.us_Username) Then
  Me.AddBrokenRuleByKey(SFSUsersBOFieldNames.us_Username, "SFST_DuplicateEntry")
End If

Private Function UserLoginNameDuplicatesExists(ByVal tcUserName As String) As Boolean

'-- Establish locals
Dim lnPK As Integer = -1
Dim loCommand As New SqlCommand()

'-- Exclude current record from comparision
If Me.Count > 0 Then
  lnPK = Me.us_pk
End If

loCommand.CommandText = "SELECT COUNT(*) FROM " & Me.TableNameAndSchema & " WHERE us_pk != @us_pk and us_UserName = @us_UserName"

loCommand.Parameters.Add("@us_pk", SqlDbType.Int)
loCommand.Parameters.Add("@us_UserName", SqlDbType.VarChar)
loCommand.Parameters("@us_pk").Value = lnPK
loCommand.Parameters("@us_UserName").Value = tcUserName

'-- Add project key if necessary
If Me.us_sproj_pk > 0 Then
  loCommand.CommandText &= " and us_sproj_pk = @sproj_pk"
  loCommand.Parameters.Add("@sproj_pk", SqlDbType.Int)
  loCommand.Parameters("@sproj_pk").Value = Me.us_sproj_pk
End If

'-- Return Results
Return CType(Me.ExecuteScalar(loCommand), Integer) > 0

End Function

Clear as mud?? w00t