What is the purpose of constraint naming

user_157566 0
Viewed 38890

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!

constraints sql naming database
9 Answers

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.

user_13581790

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.

user_52973800

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.

user_1805290
Related