[RESOLVED] Composite Table Issues - Does not match table definition

[BrailleSchool]

Hi, all,

I have a composite table called TicketAuthors that has four columns. Three of those are set to primary key. I have found that you can't have a NULL value with a composite table with multiple primary keys.
The issue I am having is that I am using a stored procedure to check to see if the name exists, if it doesn't, then to add the name.

Only problem is, I am getting the error in the attached screen shot.
I have found that the table is removing the identity specification for two of the three primary key columns.
I am not sure what to do about this as this is the first time I am working with a composite primary key.
I have been searching for hours for a resolution and haven't found one yet.

Stored Procedure:

ALTER PROCEDURE dbo.CheckTicketAuthorExists(@User VARCHAR(250))
AS
BEGIN
IF (SELECT COUNT(*) FROM TicketAuthors WHERE TicketAuthors = @User) = 0
BEGIN
INSERT INTO TicketAuthors
VALUES(@User)
END
END

Table setup:

CREATE TABLE [dbo].[TicketAuthors](
	[CreatedByID_PK] [int] NOT NULL,
	[LastModifiedByID_PK] [int] NOT NULL,
	[BeingViewedByID_PK] [int] IDENTITY(1,1) NOT NULL,
	[TicketAuthors] [varchar](250) NOT NULL,
 CONSTRAINT [PK_TicketAuthors] PRIMARY KEY CLUSTERED 
(
	[CreatedByID_PK] ASC,
	[LastModifiedByID_PK] ASC,
	[BeingViewedByID_PK] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

All the three public key columns need to have the same value. Please advise as to your thoughts on how to get this working.

[si_the_geek]

You haven't specified the fields to insert the value(s) into, so the assumption is that you want to insert in to all fields... but you haven't supplied enough values.

Try adding the field, like this:

INSERT INTO TicketAuthors (TicketAuthors)
VALUES(@User)

[techgnome]

And yet that won't work either, since the three fields that make up the PKEy will need to be filled in - they are PKey for starters, and secondly they're listed as NOT NULL...
then there's this little nugget:

All the three public key columns need to have the same value.

Eeeh... that doesn't make sense, especially since one of them is an Identity field...
Or what that supposed to be "some" value, not "same" value.

-tg

[BrailleSchool]

The application has a list of end users in one table. the main table where most of the information is stored links to that table using a PK FK relationship.
Required by management to be able to see who created the entry, who last modified it and who is viewing it (if applicable).
The quick way is to just insert their name into each field respectively, but they (management) wants the users in their own table and then linked to the primary table.
Different users may be displaying the record on screen, created it to begin with or even last modified it.
I was thinking like a table in between the two (not sure what that is), but using the identity of the end user to reference the person or people who is currently viewing, last modified and initially created.

[techgnome]

Right... that makes some sense... what didn't was this:
All the three public key columns need to have the same value.

Which sounded like you have to have 1,1,1 or 2,2,2... and so on... that's the part that didn't make sense... what did was this: 1,2,1, 1,2,2, 2,2,3, 2,1,4 ... and so on...
Something you may want to consider... we also do auditing on who creates and updates our records too (we don't care who last viewed it, as it's a useless statistic to be honest) ... so our tables have a CREATEDBYID and a CHANGEBYID ... both foreign keys to the users table... when a record is added, the CREATEDBYID and the CHANGEDBYID are both set to the same... after that the CRETEDBYID is never touched again, and only the CHANGEDBYID gets updated (there's also corresponding fields to track the DATEADDED and DATECHANGED) ... this allows us to know who created the record and when, as well as who last updated it and when. But I guess where I was going is that it's on the table itself, it's not in a different table.

-tg

[BrailleSchool]

If I am understanding correctly... you have one table with the end users that have a PK assigned to that user. In the main table where the main data lives, you have three FK fields that are updated manually based on the event?
I do have two datetime fields, one for CreatedOn and the other ModifiedOn. I don't need one for viewed on as that is useless information.

The issue I had is if I attempted a join on those two tables, I could get the CreatedBy, but was not able to pull the information based on ModifiedBy. The value wouldn't populate in the SQL query. This is why I was considering the composite table, but then that caused a headache as composite tables cannot have NULLS in the columns. I am wondering if I even need to do a composite table... doesn't sound like it.

[techgnome]

No, we only have two fields... created and changed by.... but yes, in short, that's what we have... so now let's say I want to pull a record and see who created it and who last edited it... it looks like this:

select 
  T.Id, T.Field1, T.Field3, C.Name as CreatedBy, E.Name as EditedBy
from SomeTable T
inner join *****ers C on T.CreatedByID = C.ID
inner join *****ers E on T.ChangedByID = E.ID

Now I see the data in the record (T) and who created it (C) and who last changed the record (E).


-tg

[BrailleSchool]

This is what the structure looks like. Does it look fine for what I need to accomplish?

[techgnome]

Ultimately only you can decide that... I'd rename the FKeys to something a bit more meaningful though... but it looks OK...

-tg

[BrailleSchool]

The thing is, with the search form, some items are mandatory and others are optional.
The form validates accordingly to prevent the end user from doing something unexpected.
Either way, regardless of what the end user types in to search, we always want to return the value for ModifiedBy.
I've updated the PROC. I don't have test records in the database to test yet, but was able to save successfully.
Does this look correct?

ALTER PROCEDURE [dbo].[SP_ObtainTicketInformation]
@TicketNumber AS INT,
@RMANumber AS INT,
@StoreNumber AS INT,
@TicketStatus AS VARCHAR(16),
@CreatedBy AS VARCHAR(250),
@DateFrom AS DATETIME,
@DateTo AS DATETIME
AS
SET NOCOUNT ON
BEGIN
SELECT * 
FROM [TICKETMANAGER].[dbo].[Tickets] AS T
JOIN [dbo].[StoreNumbers] AS SN
ON SN.StoreNumberID = T.StoreNumberID
JOIN [dbo].[TicketAuthors] AS CreatedBy
ON CreatedBy.UsersID = T.CreatedBy
JOIN [dbo].[TicketAuthors] AS ModifiedBy
ON ModifiedBy.UsersID = T.LastModifiedBy
JOIN [dbo].[TicketStatus] AS TS
ON TS.TicketStatusID = T.TicketStatusID
WHERE T.TicketID = @TicketNumber
OR T.RMANumber = @RMANumber
OR T.StoreNumberID = @StoreNumber 
OR T.TicketStatusID = @TicketStatus
OR CreatedBy.UsersID = @CreatedBy
OR T.DateCreated BETWEEN @DateFrom AND @DateTo AND T.DateResolved IS NOT NULL
AND ModifiedBy.UsersID = T.LastModifiedBy
END

Or would it be this because you only need to do one join on a table to get the information:

ALTER PROCEDURE [dbo].[SP_ObtainTicketInformation]
@TicketNumber AS INT,
@RMANumber AS INT,
@StoreNumber AS INT,
@TicketStatus AS VARCHAR(16),
@CreatedBy AS VARCHAR(250),
@DateFrom AS DATETIME,
@DateTo AS DATETIME
AS
SET NOCOUNT ON
BEGIN
SELECT * 
FROM [TICKETMANAGER].[dbo].[Tickets] AS T
JOIN [dbo].[StoreNumbers] AS SN
ON SN.StoreNumberID = T.StoreNumberID
JOIN [dbo].[TicketAuthors] AS TA
ON TA.UsersID = T.CreatedBy
JOIN [dbo].[TicketStatus] AS TS
ON TS.TicketStatusID = T.TicketStatusID
WHERE T.TicketID = @TicketNumber
OR T.RMANumber = @RMANumber
OR T.StoreNumberID = @StoreNumber 
OR T.TicketStatusID = @TicketStatus
OR TA.UsersID = @CreatedBy
OR T.DateCreated BETWEEN @DateFrom AND @DateTo AND T.DateResolved IS NOT NULL
AND TA.UsersID = T.LastModifiedBy
END

[techgnome]

Except for the SELECT * part... and the "AND ModifiedBy.UsersID = T.LastModifiedBy" in the where clause (which is the same as the join clause, so it's not needed in the where) ...


-tg

[BrailleSchool]

So, by doing a JOIN, I am doing the same as a FULL JOIN, right?
If so, this would then mean I am getting all fields that are linked to the main table.
How would I extract the ModifiedBy name? When I tried this yesterday with some tickets, all the tickets were showing the right information, except when there was someone in the ModifiedBy column. It was showing the incorrect name. There won't be any NULL values in this column as there is one called N/A.
The other option I have is to set a default on that column to automatically add a value to the column when the record is saved, but then still have the problem of pulling the right name from the table. The PK of the CreatedBy and the PK of the ModifiedBy were different, but were pulling the same name.
SQL is not my strong point and PROCS are even more than a weak link for me. Have to start somewhere though.

[BrailleSchool]

Except for the SELECT * part... and the "AND ModifiedBy.UsersID = T.LastModifiedBy" in the where clause (which is the same as the join clause, so it's not needed in the where) ...


-tg

I removed the SELECT * and when going to save the PROC, I was told it was required.

The PROC currently is like so. I'll test it when I have some test records to see what happens.

ALTER PROCEDURE [dbo].[SP_ObtainTicketInformation]
@TicketNumber AS INT,
@RMANumber AS INT,
@StoreNumber AS INT,
@TicketStatus AS VARCHAR(16),
@CreatedBy AS VARCHAR(250),
@DateFrom AS DATETIME,
@DateTo AS DATETIME
AS
SET NOCOUNT ON
BEGIN
SELECT *
FROM [TICKETMANAGER].[dbo].[Tickets] AS T
JOIN [dbo].[StoreNumbers] AS SN
ON SN.StoreNumberID = T.StoreNumberID
JOIN [dbo].[TicketAuthors] AS TA
ON TA.UsersID = T.CreatedBy AND TA.UsersID = T.LastModifiedBy
JOIN [dbo].[TicketStatus] AS TS
ON TS.TicketStatusID = T.TicketStatusID
WHERE T.TicketID = @TicketNumber
OR T.RMANumber = @RMANumber
OR T.StoreNumberID = @StoreNumber 
OR T.TicketStatusID = @TicketStatus
OR TA.UsersID = @CreatedBy
OR T.DateCreated BETWEEN @DateFrom AND @DateTo AND T.DateResolved IS NOT NULL
END

[BrailleSchool]

I have removed "AND TA.UsersID = T.LastModifiedBy" as the join should get this information already.
Not sure why I put it in there when it should already exist in the JOIN.

[techgnome]

As a rule I never use JOIN by itself... as I'm never sure what kind of join I'm going to get... I always specify it. I think most DBMSs use INNER JOIN as the default.. .but since I always specify OUTER, INNER, LEFT, RIGHT or CROSS... I don't know what a plain join does. I'm anal like that I want to know what is happening with my data at all times. I'm a control freak with data.


As for the SELECT * sigh... Generally speaking it's a bad idea because it means you're going to get EVERYTHING FROM EVERYTABLE... except for diagnostic reasons, there is never a reason to do that... so as rule #1 in SQL-ing, I always specify the fields explicitly. (My join rule above is #3) It can be a pain, but it's worth it in the long run.


If you're not getting what you think you should be getting, break down your query... cut out the extra tables... just include the main table and the user table joined on the modifiedby field... if you get the right results (this is one of those diagnostic times I was talking about where breaking rule #1 is ok) then add the other tables in one by one.... keep in mind that you're joining to the same table more than once... so make sure when you see the user info you're seeing the right one, and not the other...

-tg

[techgnome]

As a rule I never use JOIN by itself... as I'm never sure what kind of join I'm going to get... I always specify it. I think most DBMSs use INNER JOIN as the default.. .but since I always specify OUTER, INNER, LEFT, RIGHT or CROSS... I don't know what a plain join does. I'm anal like that I want to know what is happening with my data at all times. I'm a control freak with data.


As for the SELECT * sigh... Generally speaking it's a bad idea because it means you're going to get EVERYTHING FROM EVERYTABLE... except for diagnostic reasons, there is never a reason to do that... so as rule #1 in SQL-ing, I always specify the fields explicitly. (My join rule above is #3) It can be a pain, but it's worth it in the long run.


If you're not getting what you think you should be getting, break down your query... cut out the extra tables... just include the main table and the user table joined on the modifiedby field... if you get the right results (this is one of those diagnostic times I was talking about where breaking rule #1 is ok) then add the other tables in one by one.... keep in mind that you're joining to the same table more than once... so make sure when you see the user info you're seeing the right one, and not the other...

-tg

[BrailleSchool]

I have removed "AND TA.UsersID = T.LastModifiedBy" as the join should get this information already.
Not sure why I put it in there when it should already exist in the JOIN.

[BrailleSchool]

Thanks for the help and suggestions. Always room for improvement.
I'll test the code and see what happens. If I don't get what I expect, then I will do one join at a time and verify. It'll take longer, but it will be worth it.

As a rule I never use JOIN by itself... as I'm never sure what kind of join I'm going to get... I always specify it. I think most DBMSs use INNER JOIN as the default.. .but since I always specify OUTER, INNER, LEFT, RIGHT or CROSS... I don't know what a plain join does. I'm anal like that I want to know what is happening with my data at all times. I'm a control freak with data.


As for the SELECT * sigh... Generally speaking it's a bad idea because it means you're going to get EVERYTHING FROM EVERYTABLE... except for diagnostic reasons, there is never a reason to do that... so as rule #1 in SQL-ing, I always specify the fields explicitly. (My join rule above is #3) It can be a pain, but it's worth it in the long run.


If you're not getting what you think you should be getting, break down your query... cut out the extra tables... just include the main table and the user table joined on the modifiedby field... if you get the right results (this is one of those diagnostic times I was talking about where breaking rule #1 is ok) then add the other tables in one by one.... keep in mind that you're joining to the same table more than once... so make sure when you see the user info you're seeing the right one, and not the other...

-tg

[BrailleSchool]

Ok. I am lost. The application passes a parameter called CreatedBy to the PROC which returns the correct value. There is no parameter being passes either way to get the ModifiedBy value. I have redone the PROC and it returns the correct information, but the ListView is not displaying the correct LastModifiedBy. How do I get the information from the table and then send that value from the table, to the PROC and then into the application?
I was looking at declaring a parameter within the PROC for an OUTPUT, but I am not sure what this exactly does.

Screen shots and code attached.

Private Sub SearchTicket_Click(ByVal sender As System.Object, _
                                   ByVal e As System.EventArgs) Handles SearchTicket.Click

        TicketView.Items.Clear()

        Using SetDatabaseConnection As New SqlConnection(MSSQLConnection)
            Try
                Dim GetTicketInformation As SqlCommand = New SqlCommand

                SetDatabaseConnection.Open()

                With GetTicketInformation
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "SP_ObtainTicketInformation"
                    .Parameters.AddWithValue("@TicketNumber", SearchTicketNumber.Text.ToString.Trim)
                    .Parameters.AddWithValue("@RMANumber", SearchRMANumber.Text.ToString.Trim)
                    .Parameters.AddWithValue("@StoreNumber", SearchStoreNumber.Text.ToString.Trim)
                    .Parameters.AddWithValue("@TicketStatus", SearchTicketStatus.Text)
                    .Parameters.AddWithValue("@CreatedBy", SearchTicketCreatedBy.Text)
                    .Parameters.AddWithValue("@DateFrom", SearchDateFrom.Value.ToString(SPROCDateFormat))
                    .Parameters.AddWithValue("@DateTo", SearchDateTo.Value.ToString(SPROCDateFormat))
                    .ExecuteNonQuery()
                End With

                Dim Ds As DataSet = New DataSet
                Dim Da As SqlDataAdapter = New SqlDataAdapter(GetTicketInformation)
                Ds.Clear()
                Da.Fill(Ds)

                With Ds.Tables(0)
                    If .Rows.Count > 0 Then
                        For Each TheDataRow As DataRow In .Rows
                            Dim lvi As ListViewItem = New ListViewItem

                            lvi = TicketView.Items.Add(CStr(TheDataRow.Item("TicketID")))
                            lvi.SubItems.Add(CStr(TheDataRow.Item("TicketStatus")))
                            lvi.SubItems.Add(CStr(TheDataRow.Item("StoreNumber")))

                            If Not IsDBNull(TheDataRow.Item("RMANumber")) Then
                                lvi.SubItems.Add(TheDataRow.Item("RMANumber"))
                            Else
                                lvi.SubItems.Add("N/A")
                            End If

                            lvi.SubItems.Add(CStr(TheDataRow.Item("DateCreated")))
                            lvi.SubItems.Add(CStr(TheDataRow.Item("TicketAuthors")))

                            If Not IsDBNull(TheDataRow.Item("DateLastModified")) Then
                                lvi.SubItems.Add(TheDataRow.Item("DateLastModified"))
                            Else
                                lvi.SubItems.Add("N/A")
                            End If

                            lvi.SubItems.Add(CStr(TheDataRow.Item("TicketAuthors")))

                            If Not IsDBNull(TheDataRow.Item("DateResolved")) Then
                                lvi.SubItems.Add(TheDataRow.Item("DateResolved"))
                            Else
                                lvi.SubItems.Add("N/A")
                            End If

                            If Not IsDBNull(TheDataRow.Item("LastModifiedBy")) Then
                                lvi.SubItems.Add(TheDataRow.Item("LastModifiedBy"))
                            Else
                                lvi.SubItems.Add("N/A")
                            End If
                        Next TheDataRow
                    Else
                        MessageBox.Show("No tickets were found using the criteria you specified. Please try again.", _
                                        "No Tickets Found", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Information, _
                                        MessageBoxDefaultButton.Button1)
                    End If
                End With

                Da.Dispose()
                Ds.Dispose()
            Catch CastEx As InvalidCastException
                MessageBox.Show(CastEx.Message, _
                                "Invalid Cast Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Catch ArgEx As ArgumentException
                MessageBox.Show(ArgEx.Message, _
                                "Argument Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Catch NullRefEx As NullReferenceException
                MessageBox.Show(NullRefEx.Message, _
                                "Null Reference Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Catch SQLEx As SqlException
                MessageBox.Show(SQLEx.Message, _
                                "SQL Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Finally
                SetDatabaseConnection.Close()
            End Try

            With TicketView
                Dim ColumnWidth As Integer
                For ColumnCount As Integer = 0 To .Columns.Count - 1
                    .Columns(ColumnCount).Width = -2
                    ColumnWidth += .Columns(ColumnCount).Width
                Next ColumnCount

                Me.ClientSize = New Size(ColumnWidth + 10, Me.ClientSize.Height)

                TicketViewDisplayCountBottom.Text = "Tickets: " & .Items.Count
            End With
        End Using
    End Sub

[techgnome]

you've got two fields with the same name - twice... look at your SQL...
you have:
CreatedBy.UsersID, CreatedBy.TicketAuthors,
ModifiedBy.UsersID, ModifiedBy.TicketAuthors

So when you run this line:
If Not IsDBNull(TheDataRow.Item("LastModifiedBy")) Then
lvi.SubItems.Add(TheDataRow.Item("LastModifiedBy"))
Else
lvi.SubItems.Add("N/A")
End If
Which isn't the right field anyways, but it's only going to get the first one... so the TicketAuthors of ModifiedBy doesn't get used...

which is why if you look at my original query:
select
T.Id, T.Field1, T.Field3, C.Name as CreatedBy, E.Name as EditedBy
from SomeTable T
inner join *****ers C on T.CreatedByID = C.ID
inner join *****ers E on T.ChangedByID = E.ID

You'll see I aliased the fields ... you need to do something similar.

-tg

[BrailleSchool]

Ok. Now I understand.
Each column name has to be unique otherwise it won't get the other values.
I am making changes here and debugging. Making progress now.
Thanks TG!

you've got two fields with the same name - twice... look at your SQL...
you have:
CreatedBy.UsersID, CreatedBy.TicketAuthors,
ModifiedBy.UsersID, ModifiedBy.TicketAuthors

So when you run this line:
If Not IsDBNull(TheDataRow.Item("LastModifiedBy")) Then
lvi.SubItems.Add(TheDataRow.Item("LastModifiedBy"))
Else
lvi.SubItems.Add("N/A")
End If
Which isn't the right field anyways, but it's only going to get the first one... so the TicketAuthors of ModifiedBy doesn't get used...

which is why if you look at my original query:
select
T.Id, T.Field1, T.Field3, C.Name as CreatedBy, E.Name as EditedBy
from SomeTable T
inner join *****ers C on T.CreatedByID = C.ID
inner join *****ers E on T.ChangedByID = E.ID

You'll see I aliased the fields ... you need to do something similar.

-tg

[BrailleSchool]

Ok. I got it working with your help. Thanks so much TG. Learn something new every day. I knew you could alias the tables, but didnt know you could do it with columns, too.
Working code below in the event any one else is having this issue and looking for an answer.

ALTER PROCEDURE [dbo].[SP_ObtainTicketInformation]
@TicketNumber AS INT,
@RMANumber AS INT,
@StoreNumber AS INT,
@TicketStatus AS VARCHAR(16),
@CreatedBy AS VARCHAR(250),
@DateFrom AS DATETIME,
@DateTo AS DATETIME
AS
SET NOCOUNT ON
BEGIN
SELECT T.TicketID, T.TicketStatusID, T.StoreNumberID, T.RMANumber, T.DateCreated, T.CreatedBy, T.DateLastModified, T.DateResolved,
       TS.TicketStatusID, TS.TicketStatus, 
       SN.StoreNumberID, SN.StoreNumber, 
       C.TicketAuthors AS CreatedByUser,
	   M.TicketAuthors AS ModifiedByUser
FROM [TICKETMANAGER].[dbo].[Tickets] AS T
INNER JOIN [dbo].[StoreNumbers] AS SN
ON SN.StoreNumberID = T.StoreNumberID
INNER JOIN [dbo].[TicketAuthors] AS C
ON T.CreatedBy = C.UsersID
INNER JOIN [dbo].[TicketAuthors] AS M
ON T.LastModifiedBy = M.UsersID
INNER JOIN [dbo].[TicketStatus] AS TS
ON TS.TicketStatusID = T.TicketStatusID
WHERE T.TicketID = @TicketNumber
OR T.RMANumber = @RMANumber
OR T.StoreNumberID = @StoreNumber 
OR T.TicketStatusID = @TicketStatus
OR C.UsersID = @CreatedBy
OR T.DateCreated BETWEEN @DateFrom AND @DateTo AND T.DateResolved IS NOT NULL
AND T.LastModifiedBy = M.UsersID
END

[techgnome]

Ok. Now I understand.
Each column name has to be unique otherwise it won't get the other values.
I am making changes here and debugging. Making progress now.
Thanks TG!

Now you're getting it.

Ok. I got it working with your help. Thanks so much TG. Learn something new every day. I knew you could alias the tables, but didnt know you could do it with columns, too.

I wouldn't be able to survive if I couldn't alias columns.

-tg