Keys in SQL Server

StrataFrame Forum

Back

Keys in SQL Server

Author	Message
Steve L. Taylor	Steve L. Taylor posted 18 Years Ago Post Details Share Post
Group: StrataFrame Developers Posts: 40, Visits: 91	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??
	Reply Like 1
Flavelle Ballem	Flavelle Ballem posted 18 Years Ago Post Details Share Post
Group: Forum Members Posts: 56, Visits: 126	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.
	Reply Like 1
Trent Taylor	Trent Taylor posted 18 Years Ago Post Details Share Post
Group: StrataFrame Developers Posts: 6.6K, Visits: 6.9K	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
	Reply Like 1
Flavelle Ballem	Flavelle Ballem posted 18 Years Ago Topic Details Share Topic
Group: Forum Members Posts: 56, Visits: 126	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
	Reply Like 1