What is the purpose of constraint naming

sql database constraints naming
Andrew picture Andrew · Sep 9, 2009 · Viewed 29.2k times · Source

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!

Answer

Philip Kelley picture Philip Kelley · Sep 9, 2009

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.

Related questions

How do I add a custom CHECK constraint on a MySQL table?

I am having trouble with this table CREATE TABLE `Participants` ( `meetid` int(11) NOT NULL, `pid` varchar(15) NOT NULL, `status` char(1) DEFAULT NULL, PRIMARY KEY (`meetid`,`pid`), CONSTRAINT `participants_ibfk_1` FOREIGN KEY (`meetid`) REFERENCES `Meetings` (`meetid`) ON DELETE CASCADE CONSTRAINT `participants_…

Read More
Deferrable Constraints in SQL Server

Do any versions of SQL Server support deferrable constraints (DC)? Since about version 8.0, Oracle has supported deferrable constraints - constraints that are only evaluated when you commit a statement group, not when you insert or update individual tables. Deferrable constraints …

Read More
Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (…

Read More