Composite Primary Key/Foreign Key Headaches

Question

I am working with SQL Server 2008 R2, C# and ASP.Net. I have a table that has a composite Primary key consisting of a ticket number, and the number of times that ticket appears in the table. The ticket frequency is calculated by the C# Code:

VTTTickets.InsertParameters[0].DefaultValue = VTTTTicketNoBox.Text;
string CommString = "SELECT COUNT(*) FROM [Tickets] WHERE [Ticket_No] = " + 
VTTTTicketNoBox.Text;
string ConnString = ConfigurationManager.ConnectionStrings[1].ConnectionString;
OdbcConnection Conn = new OdbcConnection(ConnString);
Conn.Open();
OdbcCommand FooCommand = newOdbcCommand(CommString,Conn);
int FooVal = Convert.ToInt32(FooCommand.ExecuteScalar()) + 1;
VTTTickets.InsertParameters[1].DefaultValue = Convert.ToString(FooVal);
VTTTTicketNoBox.Text = "";
Conn.Close();

My tables constraint/etc code

CONSTRAINT [PK_Tickets] PRIMARY KEY CLUSTERED 
([Ticket_No] ASC, [Ticket_Sub_No] ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = ON,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
CONSTRAINT [Unique_Ticket_No] UNIQUE NONCLUSTERED 
([Ticket_No] ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = ON,     
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
) ON [PRIMARY]
GO

The reason for the unique constraint is because that part of the primary composite key is also a foreign key for two other tables. The error I keep getting is when it yells at me for there being a duplicate values present in the unique constrained column. This may occur once everything moves to production from development, as a ticket may be re-submitted.

Is there any way to make the foreign key without the unique key constraint? If not, how do I get around this problem?

Answer 1

This is one of those situations that we run into a lot in programming. Technology_A will not let me do Operation_B, but it should! Then, after beating our heads against the wall for a while, we give up (or head to Stackoverflow). We do this because we're not using the technology as it was intended (which is OK, that's how we learn!)

The issue here is your database schema.

You are trying to do too much with one table. You should not store tickets (where the same ticket may exist more than once) in the same table that you are tracking # of occurrences.

In my opinion, there are two good strategies to correct this situation. First, I would create a tickets table, with a single-column Primary Key. Then, I would create a table that stores each instance of a ticket.

Example: Ticket43 is closed, but re-opened and closed again. This means (if I read your question right), that the ticket had two instances. This means it would have two entries in your original table, but in my new proposed schema, it would have one entry in Tickets, and two entries in Ticket_Instances.

Note: You will want to make sure to store ticket information that never changes between instances in Tickets, and instance-specific Ticket information in Ticket_Instances.

To record Ticket Counts, the first thing I would do is simply write a view or bit of SQL like the following:

SELECT
  count(*) as TicketCount,
  TicketID
FROM
  Ticket_Instances as TI
GROUP BY
  TicketID

If you do not want to have to calculate this on demand, then I suggest making use of:

• ASP.NET Cache (Run the above SQL, jam it into cache with a TTL of 10 minutes)
• Use the Ticket_Counts table that is populated by a trigger

I suspect you will prefer option 2 there (although I would use option 1).

Trigger Method:

Assuming Ticket_Instances may never be deleted, you only need an Insert Trigger. You would create a trigger on the Ticket_Instances table upon Insert, and that trigger SQL would do the following:

• If the TicketID does not exist in Ticket_Counts, then Insert the TicketID into Ticket_Counts with TicketCount of 0
• Then, Increase the TicketCount by 1 of the TicketID in Ticket_Counts

With this method, you only need to access Ticket_Counts by TicketID to get the # of occurrences of that particular Ticket.

I think you will find that your constraint errors disappear once you update your schema.