| Author |
Topic |
|
rbrady
Starting Member
5 Posts |
 Posted - 2009-03-27 : 10:05:17
|
| I have a problem and I was wondering the best way to approach it. Maybe my thinking is wrong on this subject. That is why I am posting it here.I have a table and I want to restrict record inserts. Let me describe the fields first:CarrierName (VarChar)- TrailerNumber (VarChar) - Active (Bit)If I try to insert a new record that has duplicate info in these three fields I am thinking I want a unique index to prevent it.Basically I have trailers in this table. The records marked active=1 are my current inventory. Records where active=0 are for history purposes. But, I do not want two trailers which are the same CarrierName and TrailerNumber to be marked Active=1Any thoughts. Much thanks! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-28 : 00:30:34
|
| Looks like you want composite PK on 2 columns. |
 |
|
|
rbrady
Starting Member
5 Posts |
Posted - 2009-03-30 : 09:59:45
|
| Here is my problem with Composite PK's: As time goes by I will eventually have the following in this table:CarrierName - TrailerNumber - Active - DateTime------------------------------------------------------------------Carrier1 - 123 - 1 - 03/30/2009 01:00:00 PMCarrier1 - 123 - 0 - 03/23/2009 12:15:00 AMCarrier1 - 123 - 0 - 03/20/2009 06:45:00 PMCarrier1 - 123 - 0 - 12/11/2008 01:00:00 PMIf I base it on CarrierName, TrailerNumber, and Active then a composite pk will not work. I will have multiple records where Active = 0 for the same carrier/trailer.If I pull the datetime into this composite pk then I could get:CarrierName - TrailerNumber - Active - DateTime------------------------------------------------------------------Carrier1 - 123 - 1 - 03/30/2009 01:00:00 PMCarrier1 - 123 - 1 - 03/23/2009 12:15:00 AMCarrier1 - 123 - 0 - 03/20/2009 06:45:00 PMCarrier1 - 123 - 0 - 12/11/2008 01:00:00 PMWhich I don't want either because I have multiple records for the same carrier/trailer that have active =1.Maybe I am not understanding your reply. If so please correct me.Thanks for your reply! |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-03-30 : 10:08:33
|
| You are right rbrady. you can have a unique constraint on the three columns which will fire off for any dup entries..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
rbrady
Starting Member
5 Posts |
Posted - 2009-03-30 : 10:46:46
|
| Do you know the best way to handle this scenario? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-03-30 : 14:58:59
|
| what you have IS the best way IMHO. If you need to maintain the uniqueness of the three columns then unique constraint is the best option.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
rbrady
Starting Member
5 Posts |
Posted - 2009-03-30 : 17:10:48
|
| I may not be explaining myself good enough. Let me try it another way:Let's say I have a table with the following data in it:CarrierName(VarChar) - TrailerNumber(VarChar) - Active(Bit) - DateTime----------------------------------------------------------------------Carrier1 - 123 - 1 - 03/30/2009 01:00:00 PMCarrier1 - 123 - 0 - 03/23/2009 12:15:00 AMCarrier1 - 123 - 0 - 03/20/2009 06:45:00 PMCarrier1 - 123 - 0 - 12/11/2008 01:00:00 PMNow I try to add another record with the following values:CarrierName - TrailerNumber - Active - DateTime----------------------------------------------------------------------Carrier1 - 123 - 1 - 03/30/2009 02:35:00 PMI do not want this record to be added to the db because the CarrierName, TrailerNumber, and Active fields are the same as an existing record in the table.How can I set my db up to keep this duplicate record from happening?Much Thanks!!! |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-03-31 : 10:49:20
|
| yes.. repeating myself.. what you need is unique constraint on those three columns..ALTER TABLE .... ADD CONSTRAINT .... UNIQUE ( CarrierName, TrailerNumber, Active)Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
rbrady
Starting Member
5 Posts |
Posted - 2009-04-01 : 07:44:39
|
| dinakar I am not trying to be stubborn. But, I tried that and it will not work. I get the following error:The CREATE UNIQUE INDEX statement terminated because a duplicate key was found....BECAUSE there are multiple records in the table that have Active=0 which are of the same CarrierName and TrailerNumber. Your solution constrains ALL duplicates in those three fields. I need to ONLY constrain when inserting a new record where active=1.In the sample data below I have Carrier1 (Trailer 123) that has multiple records where active=0.Once I add a record for a trailer it stays in the db as history. All I do is set Active=0 when a particular record is now a history record. All NEW records get added with Active=1. There can be MULTIPLE records for the same CarrierName, TrailerNumber combination.CarrierName(VarChar) - TrailerNumber(VarChar) - Active(Bit) - DateTime----------------------------------------------------------------------Carrier1 - 123 - 1 - 03/30/2009 01:00:00 PMCarrier1 - 123 - 0 - 03/23/2009 12:15:00 AMCarrier1 - 123 - 0 - 03/20/2009 06:45:00 PMCarrier1 - 123 - 0 - 12/11/2008 01:00:00 PMI REALLY do appreciate your help but it does not solve my problem. I am wondering if I have a flaw in the way I am trying to design this db. But I am really not sure. That is the purpose of this thread. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-04-01 : 11:05:20
|
| ahh I see... so you want selective constraint? you want allow multiple records with Active = 0 but not with Active = 1. This can be achieved with logic in stored proc. You need to check before inserting/updating if there is already a record. There is no build-in way to do that.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-04-01 : 15:16:53
|
| Another alternative to the stored procedure approach is to add the logic in an insert/update trigger. It carries some overhead but it does insure that no one can create two active carrier/trailers by circumventing the procedure.=======================================Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727) |
|
|
|
|
Help with Index Problem