StrataFrame Forum » StrataFrame Database Deployment Toolkit » Issues » Keys in SQL Server

Keys in SQL Server

Flavelle Ballem — Wed, 20 Sep 2006 16:28:04 GMT

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

RE: Keys in SQL Server

Steve L. Taylor — Wed, 20 Sep 2006 16:28:04 GMT

Hello Flavelle,

[quote]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. [/quote]

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:

RE: Keys in SQL Server

Flavelle Ballem — Tue, 19 Sep 2006 17:16:56 GMT

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.

RE: Keys in SQL Server

Trent L. Taylor — Mon, 18 Sep 2006 09:56:41 GMT

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