| Author |
Topic |
|
BMcDonald
Starting Member
8 Posts |
 Posted - 2012-03-19 : 12:43:03
|
| Hello,Within the CRM database that I manage (that uses a SQL 2005 back end) - we have a business rule that needs to now be enforced. That is - only one named organization can exist at any given real world address. Historically - I have not enforced this rule at either app level of the SQL Level as I continued to use the "benefit of the doubt" clause with our users - hoping that they could understand that only one org with a given name can exist at a single real address - cabut now a situation has occurred where the rules need to be applied.So - I am writing some code for the application with compares the Orgname and Address entered to see if there are other records that match - but wanted a quick primer on how to create a constraint on SQL Server that would enforce this rule at the SQL table level.Appreciate any tips that can point me in the right direction.Cheers,Bruce |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-19 : 14:14:31
|
| do you already have another primary key identified in your org table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
BMcDonald
Starting Member
8 Posts |
Posted - 2012-03-19 : 16:00:13
|
quote:
Originally posted by visakh16
do you already have another primary key identified in your org table?Yes. Org_ID is the primary key for my Org table. Bruce------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-19 : 16:05:44
|
| ok...then unique index should suffice------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-20 : 15:01:19
|
| only one named organization can exist at any given real world addresssound like indewx should be on OrgName,Address------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
BMcDonald
Starting Member
8 Posts |
Posted - 2012-03-21 : 13:12:02
|
quote:
Originally posted by visakh16
only one named organization can exist at any given real world addresssound like indewx should be on OrgName,Address
Correct. Any tips on how to apply this in SQL Management Studio?Cheers!B |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
BMcDonald
Starting Member
8 Posts |
Posted - 2012-03-22 : 10:55:17
|
quote:
Originally posted by X002548
aren't you writing code?CREATE INDEX....
If I were that proficient at T-SQL code - I wouldn't need to stop by here - I am hoping that folks that choose to do some things via the SQL Tools/GUI method are not totally kicked to the curb :)B |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-03-22 : 11:13:08
|
| ummm...I guess there are quite a few..then they all change over to writing codeHow do you plane to Promote changes in different environments if you don't write code?If you have several environments (dev/qa/ust/prod/DR) do you plan to manually make the changes in every environment?It will become a HUGE MessIf you are doing this directly in Production, then what about your dev environments?If you really want to use the GUIIn SSMS, Click on the database folder and expand itThe Expand the tables folderThe open the folder for the table you wantRight Click on the index folder and select New IndexOff you goGod Luck, and may God Bless...Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
BMcDonald
Starting Member
8 Posts |
Posted - 2012-03-22 : 11:20:17
|
quote:
Originally posted by X002548
ummm...I guess there are quite a few..then they all change over to writing code. How do you plane to Promote changes in different environments if you don't write code?If you have several environments (dev/qa/ust/prod/DR) do you plan to manually make the changes in every environment?
You must have me confused with some hot-shot SQL developer at some huge company with multiple servers/environments etc:)No - I have a single production box and that's it. It's solid, works well and if our prima-donna sales people would get with the program and let me manage their database for them - we wouldn't have this issue:)Thank you for the pointers. Will give it a shot.B |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-03-22 : 12:31:38
|
quote:
Originally posted by visakh16
only one named organization can exist at any given real world addresssound like indewx should be on OrgName,Address------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I think that Addrss needs to be the unique index alone.Adding orgname to it allows many orgnames at the same address. |
|
|
|
BMcDonald
Starting Member
8 Posts |
Posted - 2012-03-22 : 13:11:45
|
quote:
[i]Originally posted by russellI think that Address needs to be the unique index alone. Adding orgname to it allows many org-names at the same address.
Correction. In the real world - there can be may orgs at the same address. Think of the address as a building downtown with 5 floors. Could have 20 orgs in the building. This index needs to be a combination of Org and Address.B |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-03-22 : 13:29:23
|
quote:
Originally posted by BMcDonald
only one named organization can exist at any given real world address.
According to this, my statement is correct. Also, multi-busineess buildings, including high-rises, have suite numbers, which make the address unique.If the above quoted text is not the requirement, fine. But if it is...How are you handling the fact that businesses move? |
|
|
|
BMcDonald
Starting Member
8 Posts |
Posted - 2012-03-22 : 13:45:19
|
quote:
Originally posted by russell
quote:
Originally posted by BMcDonald
only one named organization can exist at any given real world address.
According to this, my statement is correct. Also, multi-busineess buildings, including high-rises, have suite numbers, which make the address unique.If the above quoted text is not the requirement, fine. But if it is...How are you handling the fact that businesses move?
1. Probably should have phrased this better(since it only really matters with orgs with multiple locations) - should be more like: For any org in the system with multiple locations - each org record must have it's own unique address. :)2. If a business moves completely - we deactivate the master org record (keeping just a stub for historical purposes with comments) and then transfer all verified assets (people, calls, orders etc) over the a new org record that is setup for the new location. The system handles all the addressing changes etc.Cheers,B |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
BMcDonald
Starting Member
8 Posts |
Posted - 2012-03-22 : 15:23:03
|
quote:
[i]Originally posted by X002548Just dump the DB and create another one for developmentBrett
There's also the fast way - and then there's everything else :)If it were only that easy....and I actually enjoyed 8 meetings, 14 phone calls and 46 emails with the IT governance committee trying to explain why I needed that dev database....B |
|
|
|
|
Best way to enforce Duplicate checks