During the conversion of the Charta Web tables to PostgreSQL I used the names that were provided for the unique key constraints when creating tables. PostgreSQL complained about duplicate names for the unique keys. It turns out that some tables use the exact same identifiers for their unique keys. The SQL standard defines that constraints should have an identifier that is unique within the entire database. I think it is best that we conform to that standard by coming up with a naming convention to prevent identifier collisions.
To start we probably should include the table identifier in the constraint identifier. This would prevent most collisions. This part can be automated very well through the SQL abstraction layer. However, we might run the risk of creating identifiers that are too long.
We also might include the constraint's purpose (primary, foreign or unique) in its identifier. I am not sure whether we need this. There exist popular naming conventions that do this but I am not sure whether it is needed.
Finally, in SQL primary key constraints can have an identifier as well. Database systems automatically create identifiers if they are not provided, however, they use different naming schemes. My feeling is that it would be beneficial if we provide explicit primary key identifiers to the SQL database system. However, should the developer do this or should the abstraction layer do it? Allowing the programmer to do it makes it more symmetric with naming other constraints. In any case, adding the primary key columns to the identifier like we do for indexes and unique keys would be a great improvement over our current primary key identifiers.
Does anyone have an opinion on this subject or already has an idea for a naming convention that would work well?