86

What is the purpose of naming your constraints (unique, primary key, foreign key)?

Say I have a table which is using natural keys as a primary key:

CREATE TABLE Order ( LoginName VARCHAR(50) NOT NULL, ProductName VARCHAR(50) NOT NULL, NumberOrdered INT NOT NULL, OrderDateTime DATETIME NOT NULL, PRIMARY KEY(LoginName, OrderDateTime) );

What benefits (if any) does naming my PK bring?

Eg. Replace:

 PRIMARY KEY(LoginName, OrderDateTime)

With:

 CONSTRAINT Order_PK PRIMARY KEY(LoginName, OrderDateTime)

Sorry if my data model is not the best, I'm new to this!

Improve this question
1
  • 1
    maybe to use the name to refer to the constraint later .. like when you want to delete\edit it ? Commented Sep 9, 2009 at 3:57

9 Answers 9

Reset to default
155

Here's some pretty basic reasons.

(1) If a query (insert, update, delete) violates a constraint, SQL will generate an error message that will contain the constraint name. If the constraint name is clear and descriptive, the error message will be easier to understand; if the constraint name is a random guid-based name, it's a lot less clear. Particulary for end-users, who will (ok, might) phone you up and ask what "FK__B__B_COL1__75435199" means.

(2) If a constraint needs to be modified in the future (yes, it happens), it's very hard to do if you don't know what it's named. (ALTER TABLE MyTable drop CONSTRAINT um...) And if you create more than one instance of the database "from scratch" and use system-generated default names, no two names will ever match.

(3) If the person who gets to support your code (aka a DBA) has to waste a lot of pointless time dealing with case (1) or case (2) at 3am on Sunday, they're quite probably in a position to identify where the code came from and be able to react accordingly.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Re: "Particulary for end-users, who will (ok, might) phone you up and ask what "FK__B__B_COL1__75435199" means." -- I'd say even for developers seeing something like "fk_payment_rental" is much more useful.
13

To identify the constraint in the future (e.g. you want to drop it in the future), it should have a unique name. If you don't specify a name for it, the database engine will probably assign a weird name (e.g. containing random stuff to ensure uniqueness) for you.

Improve this answer

6 Comments

Does this mean that naming it is only used so that people can identify a constraint more easily? In other words, it doesn't matter to (or effect) the DBMS in any way whether you name a constraint or not? You can't use it in code for some purpose? Sorry if that was unclear.
It's just a name. The name doesn't make a functional difference. If you want to reference it in code in the future, the name matters, of course. It's like say, a variable name in code.
Just like column names don't make a difference. If they describe what they are ("ProductId" instead of "BJZ0_340" or "Fred"), they're much more useful.
Could you use a constraints name in a REFERENCES statement?
No. You can use it in ALTER TABLE DROP CONSTRAINT [name] for instance.
|
8

It keeps the DBAs happy, so they let your schema definition into the production database.

Improve this answer

1 Comment

Haha...yes, I guess this is the most important reason :P
3

When your code randomly violates some foreign key constraint, it sure as hell saves time on debugging to figure out which one it was. Naming them greatly simplifies debugging your inserts and your updates.

Improve this answer

Comments

2

It helps someone to know quickly what constraints are doing without having to look at the actual constraint, as the name gives you all the info you need.

So, I know if it is a primary key, unique key or default key, as well as the table and possibly columns involved.

Improve this answer

Comments

1

By correctly naming all constraints, You can quickly associate a particular constraint with our data model. This gives us two real advantages:

  1. We can quickly identify and fix any errors.
  2. We can reliably modify or drop constraints.
Improve this answer

Comments

0

By naming the constraints you can differentiate violations of them. This is not only useful for admins and developers, but your program can also use the constraint names. This is much more robust than trying to parse the error message. By using constraint names your program can react differently depending on which constraint was violated.

Constraint names are also very useful to display appropriate error messages in the user’s language mentioning which field caused a constraint violation instead of just forwarding a cryptic error message from the database server to the user.

See my answer on how to do this with PostgreSQL and Java.

Improve this answer

Comments

0

While the OP's example used a permanent table, just remember that named constraints on temp tables behave like named constraints on permanent tables (i.e. you can't have multiple sessions with the exact same code handling the temp table, without it generating an error because the constraints are named the same). Because named constraints must be unique, if you absolutely must name a constraint on a temp table try to do so with some sort of randomized GUID (like SELECT NEWID() ) on the end of it to ensure that it will uniquely-named across sessions.

Improve this answer

Comments

0

Another good reason to name constraints is if you are using version control on your database schema. In this case, if you have to drop and re-create a constraint using the default database naming (in my case SQL Server) then you will see differences between your committed version and the working copy because it will have a newly generated name. Giving an explicit name to the constraint will avoid this being flagged as a change.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.