Jump to content
#StayAtHome ×
SaranacLake

Naming Constraints

Recommended Posts

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?!

 

 

Share this post


Link to post
Share on other sites
12 minutes ago, Barand said:

Disney character names for constraints

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

Share this post


Link to post
Share on other sites

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 :))

Share this post


Link to post
Share on other sites

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.)

 

 

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites
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!

 

Share this post


Link to post
Share on other sites
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.

 

  • Like 2

Share this post


Link to post
Share on other sites
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!

 

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.