Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
michael.appleton
Posting Yak Master
160 Posts |
 Posted - 2010-08-18 : 11:32:16
|
| Here's something that's been bugging me for a while. The advice everywhere I read about constraints is to name them explicitly and use a consistent naming rule. Seems good advice, especially as the generated names contain random strings of numbers.So I've been using the approach stated below for any new table I've created, but looking at some old tables, the naming convention isn't consistent. So to save time, I decided it would be easier to write a script to rename all the foreign and primary key constraints using the rules I have come up with. Then all I need to do is run this after I create any table and my the constraint names are always correct.Niggling in the back of my head the whole time was this; if I can define rules for naming based on different attributes, namely the tables and columns involved, then why have a name at all? Same goes for indexes. Not null constraints are not named, so why a different approach for other constraints? Okay, check constraints should probably have their own names to summarise what could be a lengthy function. Even check constraints shouldn't need a unique name in the database though, why not just a unique name in the table they check on? I mean imagine if every column in the database was required to have a unique name, what a headache! That's precisely how I feel about naming constraints.Just to make things clearer my rules are this: Primary Keys: PK_[TableName] Foreign Keys: FK_[TableName]#[Column1]#[Column2]...other columns_[ForeignTableName] Indexes: IX_[TableName]#[Column1]#[Column2]...other columns..._[IndexNumber*] Unique Indexes: UQ_[TableName]#[Column1]#[Column2]...other columns..._[IndexNumber*] Default Constraints: DF_[TableName]#[ColumnName] Check constraints: CK_[TableName]#[ColumnName]#[DescriptiveName**]*this is used if there is more than one index on the same columns, which shouldn't be very common**Something that describes the constraint e.g. MinTemperatureThese kind of naming rules are very similar to anything else I have come across.Perhaps the idea of names like this is to enable more complex constraints across multiple tables that don't exist in todays database engines in the future. Any thoughts at all? Anyone else get annoyed by this, seemingly pointless, exercise of naming everything? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-18 : 12:39:13
|
| What if you have an update script to get you to Version 2.There were several patches for Version 1. 1.1, 1.2, 1.3, ... Each of them may have added / removed / changed constraints.In your Version 2 script you want to DROP a Foreign Key if it exists, and recreate it.You don't care what the referenced columns might be - in fact, lets say that it has chopped and changed between columns in the various versions - and a second FKey has been introduced at 1.3 which is the same as one in version 1.1, but with a different name, and the original was dropped in 1.2 and recreated with different columns.There isn't really any way in Version 2 you can say "Drop the FKey that references Col1 and Col2 - because that changed from 1.1 to 1.2, and again between 1.2 and 1.3But conversely if you say "Drop FKey with the name XXX" you are consistent with previous scripts and the evolution / gestation that the system went thorough in 1.1, 1.2, 1.3 ... |
 |
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2010-08-19 : 08:31:48
|
I'm a bit confused by what you're describing here. For example:quote:
and a second FKey has been introduced at 1.3 which is the same as one in version 1.1, but with a different name
Why would it have a different name if it's the same? Would meaning would this name hold? My understanding is that good naming conventions are derived from the tables and columns the foreign keys are based on. Also:quote:
and the original was dropped in 1.2 and recreated with different columns.
So you drop a foreign key then create another with different columns? How is this the same foreign key? Isn't this just two different foreign keys, unrelated in any way to each other.Perhaps I'm missing something here. I'd be interested to know what naming convention you use for your foreign keys, perhaps that'll help me understand what you mean.Thanks for your reply. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-08-19 : 10:15:16
|
I personally wouldn't include the column names in a foreign key constraint, just the table names. It's rare to need more than one FK between the same tables, and would likely indicate poor/improper design, so including the column names would be redundant. Same applies to primary keys, there's only one on the table, the column(s) don't really matter.I don't think the "exercise" of explicitly naming everything is pointless. If you really believed that, you'd still be using the template switchboard databases in MS Access. You'd also number your kids instead of naming them.  (that'll make twins/triplets easy to distinguish)Lastly, you wouldn't even bother adding the constraints or indexes at all, if naming them is such a chore. I think your real gripe is having to name/rename them afterwards instead of including them up front, which is easy to address by requiring explicit names at design time.I just recently posted a blog on defaults and user-defined types you might find helpful: http://weblogs.sqlteam.com/robv/archive/2010/08/17/how-to-make-your-databases-better-despite-microsofts-best-practices.aspxI didn't blog about rules yet but there's a link in there you should read. Using them wisely will address your concerns about multiple constraints, unique names for everything, etc., and should make the process a lot easier for you. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-19 : 11:05:57
|
| "My understanding is that good naming conventions are derived from the tables and columns the foreign keys are based on"There are lots of good naming conventions. We name FKeys using just Source and Destination tables (like Rob said) - but then we need a tie-break if a table is referenced twice (Invoice Address and Delivery Address both referencing the Address table)Including the names of the columns gets very unwieldy for multi-part keysBut, if you did have such a naming convention, then you would be able to do as you describe. IME there are always "workarounds" that become needed, and always having a Name for constraints means your Script can be sure it it dropping the intended object. |
|
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2010-09-13 : 11:03:14
|
| Sorry it's been a for a reply. I would be very interested to see the naming conventions you use. In my mind, all the names could be simplified by only having to be unique to the table they reference at the very least. Imagine each column having to be unique in the whole database! This is just as long winded to me. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-13 : 11:48:17
|
quote:
Sorry it's been a for a reply. I would be very interested to see the naming conventions you use. In my mind, all the names could be simplified by only having to be unique to the table they reference at the very least.
This works if you make them indexes, not constraints. You can't declare them as primary key though, only unique.quote:
Imagine each column having to be unique in the whole database! This is just as long winded to me.
Only object names need to be unique, and only within each database schema. Constraints being objects makes this a little more complicated, but there's nothing inherently "long winded" about it; you can name them anything, the trade off becomes how descriptive the names are (or not). |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-13 : 12:02:29
|
| Sorry, I should clarify my last post: a unique index could replace a unique constraint and avoid the naming issues associated with constraints. It is NOT possible for primary and foreign key constraints. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-13 : 14:23:02
|
quote:
Originally posted by michael.appleton
Imagine each column having to be unique in the whole database! This is just as long winded to me.
Our column names are unique within the database.We also use the column names as variable names in Sprocs and the application.If we ever have to change the attributes of a column we can do a global find across all our source code and be sure we have found everything that references the column (including Constraint names ) |
|
|
|
|
|
|
|
|
Why have constraint, key and index names?