Re: Idea: Avoid JOINs by using path expressions to follow FKs

On Tue, Mar 30, 2021, at 11:21, Joel Jacobson wrote:
> On Tue, Mar 30, 2021, at 10:24, Pavel Stehule wrote:
>> For our example data model, this would produce:
>>>
>>> ALTER TABLE public.orders RENAME CONSTRAINT orders_customer_id_fkey TO customers;
>>> ALTER TABLE public.order_details RENAME CONSTRAINT order_details_order_id_fkey TO orders;
>>> ALTER TABLE public.order_details RENAME CONSTRAINT order_details_product_id_fkey TO products;
>>
>> you fix one issue, but you lost interesting informations
>
> No, it's not lost. It's still there:
>
> # \d order_details
> Foreign-key constraints:
> "orders" FOREIGN KEY (order_id) REFERENCES orders(order_id)
> "products" FOREIGN KEY (product_id) REFERENCES products(product_id)
>
> You can still easily find out what tables/columns are referencing/referenced,
> by using \d or look in the information_schema.
>
> The primarily reason why this information is duplicated in the default name,
> is AFAIK due to avoid hypothetical name conflicts,
> which is only a real problem for users who would need to export the schema
> to some other SQL database, or use apps that depend on the names to be
> unique within the namespace, and not just within the table.
>
> The comment in pg_constraint.c explains this:
>
> /* Select a nonconflicting name for a new constraint.
> *
> * The objective here is to choose a name that is unique within the
> * specified namespace. Postgres does not require this, but the SQL
> * spec does, and some apps depend on it. Therefore we avoid choosing
> * default names that so conflict.
>
> /Joel

Users who have decided to stick to PostgreSQL for ever,
and don't have any apps that depend on (the IMHO stupid) decision by the SQL standard
to require constraints to be unique per namespace, can and should happily ignore this restriction.

/Joel