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?

Answer 1

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

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