Named CONSTRAINT benefits

Question

I'm learning SQL and stumbled about CONSTRAINT. I can define them like this:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

and like this:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

Why do I give them names? or Why I should or should not give them names? As I can see in this example and most situations in my mind I can't reuse them. So what is the benefit of giving a name to a CONSTRAINT?

score 10 · Accepted Answer · answered May 05 '11 at 12:53

There are significant benefits of giving explicit names to your constraints. Just a few examples:

You can drop them by name.
If you use conventions when choosing the name, then you can collect them from meta tables and process them programmatically.

score 4 · Answer 2 · answered May 05 '11 at 12:58

It seems you are using PostgreSQL and there the difference isn't actually that big.

This is because a system generated name in PostgreSQL is actually somewhat meaningful. But "positive_price" is still easier to understand than "foo_price_check":

Think about which error message is better to understand:
new row for relation "foo" violates check constraint "foo_price_check"
or
new row for relation "foo" violates check constraint "positive_price"

In Oracle this is even worse because a system generated does not contain any hint on what was wrong:
ORA-02290: check constraint (SYS_C0024109) violated
vs.
ORA-02290: check constraint (POSITIVE_PRICE) violated

SQL Server is somewhere in the middle, but suffers somewhat from the same problem as Oracle. — Bill, May 05 '11 at 13:04

score 2 · Answer 3 · answered May 05 '11 at 12:51

You don't specify RDBMS. the following points apply to SQL Server and I guess quite likely other RDBMSs too.

You need to know the name of the constraint to drop it, also the name of the constraint appears in constraint violation error messages so giving an explicit name can make these more meaningful (SQL Server will auto generate a name for the constraint that tells you nothing about the intent of the constraint).

score 1 · Answer 4 · answered May 05 '11 at 12:53

Constraints as object in SQL, in the same manner that a PK, FK, Table or almost anything else is. If you give your constraint a name, you can easily drop it if required, say for example during some sort of bulk data import. If you don't give it a name, you can still drop it, however you have to find out the auto-genreated name that SQL will give it.

score 1 · Answer 5 · answered May 05 '11 at 12:57

1

If your constraint is violated, having its name in the error message helps to debug it and present the error message to the user.

answered May 05 '11 at 12:57

Quassnoi

413,100
91
616
614

score 0 · Answer 6 · answered Mar 21 '20 at 18:34

Named constraint have scenarios where they are really useful. Here are the ones I have encountered so far:

Schema compare tools

If you ever need to compare environment like DEV and UT to see differences, you may got "false possitives" on table level which is really annoying. The table definition is literally the same, but because autogenerated name is different, it is marked as change.

Yes, some tools allow to skip/ignore constraint name, but not all of them.

Deployment script from state-based migration tool

If you are using tools for state-based migration tool like MS SSDT, and then you manually review generated changes before applying them on PRODUCTION, you want your final script to be as short as possible.

Having a hundred of lines, that do drop constraint and create it with different name is "noise". Some constraint could be disabled from that tool, some are stil regenerated(DEFAULT/CHECK). Having explicit name and a good naming convention solves it for all.

EIBTI

Last, but not least. Being explicit simplifies things. If you have explicit name, you could refer that database object without searching system/metadata views.

Named CONSTRAINT benefits

6 Answers6

Linked