Naming Constraints

[SaranacLake]

Do you name your Constraints?

And if so, what naming convention do you use?

 

I suppose I can see where naming Indexes maybe isn't as useful, but if you have a bunch of Constraints (e.g. Foreign Key, Check, etc) then I would think you would want to keep them organized?!

 

 

[Barand]

Disney character names for constraints

[SaranacLake]

12 minutes ago, Barand said:

Disney character names for constraints

I see you're not in a serious mood today....

[Barand]

As with the example I gave earlier for an index name (idx_xxx_yyy) then use similar for, say, foreign key constraints (FK_xxx_yyy)

Keep them logical, meaningful and unique (eg FK_Pinocchio )

[SaranacLake]

I'm reading a blog that suggests using table aliases in your constant names.

For example, if you have...

	order AS ord
	order_details  AS ordd
	

 

Then your FK constraint would be....

	fk_ord_ordd
	

 

This article also suggests using a Table Alias to pre-pend your Column Names.  (Interesting idea, although that would require a lot of thought up front.)

 

 

[Barand]

That would the meet the requirement for "logical, meaningful and unique". Beware name clashes though. They (constraint names) need to be unique within the scope of the  database schema. Table alias name scope is only the query.

 

[SaranacLake]

1 minute ago, Barand said:

That would the meet the requirement for "logical, meaningful and unique".

Well, it is too late for me to prefix the Table alias onto my Column names, but maybe I can do that for v2.?

 

1 minute ago, Barand said:

Beware name clashes though. They (constraint names) need to be unique within the scope of the  database schema.

Which one could argue is a reason to let MySQL do it for you.

Or to my point, it is a reason to come up with a good naming convention!

 

 

1 minute ago, Barand said:

Table alias name scope is only the query.

Interesting, I didn't know that!

 

[kicken]

5 hours ago, SaranacLake said:

This article also suggests using a Table Alias to pre-pend your Column Names.

Don't do that.  Not in the actual table at least.  Some people recommend this stupidity to try and avoid name collisions in their queries (such as two tables have a Label column) but such issues can be easily handled using the table.column syntax in your query rather than cluttering up column names in the table. 

SELECT o.Label as o_label, s.Label as s_label
FROM order o
INNER JOIN status s ON s.Id=o.Status

One of the applications I work on was original designed using a scheme like that where every column has a table specific prefix to it and it's super annoying (long names, broken autocomplete) for no real benefit. I've been slowly undoing that when I can and just giving the columns nice simple names. 

I'd also suggest just using the full table name in your constraint names rather than some alias.  It makes things very clear when someone 6 months later needs to decipher things.

 

[SaranacLake]

17 hours ago, kicken said:

Don't do that.  Not in the actual table at least.  Some people recommend this stupidity to try and avoid name collisions in their queries (such as two tables have a Label column) but such issues can be easily handled using the table.column syntax in your query rather than cluttering up column names in the table. 

Yeah, I thought about that afterwards and agree that there is no need for it.  It also hurts readability!

 

17 hours ago, kicken said:

SELECT o.Label as o_label, s.Label as s_label
FROM order o
INNER JOIN status s ON s.Id=o.Status

Yes, this is the format I follow now.

 

17 hours ago, kicken said:

One of the applications I work on was original designed using a scheme like that where every column has a table specific prefix to it and it's super annoying (long names, broken autocomplete) for no real benefit. I've been slowly undoing that when I can and just giving the columns nice simple names. 

Yeah.

 

17 hours ago, kicken said:

I'd also suggest just using the full table name in your constraint names rather than some alias.  It makes things very clear when someone 6 months later needs to decipher things.

True.

 

I have been Googling different naming conventions and hope to settle on something tonight.  There are lots of interesting ideas with pros/cons to each.

I strive to make everything I do be self-documenting, but this is one area I forgot about until I started creating my test database for my entitlements, and I realized this was a topic that needed a deeper dive to do it right!