What are pros and cons of different ways to create a constraint in SQL? [closed]

Question

Although, it could look like primary opinion based question, it is not absolutely the case.

I wonder what are pros and cons for each of possible conventions of creating constraints in SQL. Since I use SQL Server, I will show three examples of creating primary key constraints, that I'm familiared with:

1. CREATE TABLE Persons (Id int NOT NULL PRIMARY KEY);
   • +: concise
   • -: generates semi-random name
   • -: can't cover more than one column
2. CREATE TABLE Persons (Id int NOT NULL, CONSTRAINT PK_Persons_Id PRIMARY KEY(Id));
   • +: midding concise
   • +: can cover multiple columns
   • +: developer knows in advance what constitutes primary key and what is pk's name
   • -: decreases creating statement reusability
3. CREATE TABLE Persons (Id int NOT NULL); ALTER TABLE Persons ADD CONSTRAINT PK_Persons_Id PRIMARY KEY (Id);
   • +: separates table creating statement from its constraints which may adds some benefits like code reusability
   • -: maybe it obscures table schema, because developer doesn't know in advance what is primary key

Those are pros and cons, that I've discovered so far. Maybe more experienced developer can point out other factors that could help to make best decision while creating a primary key and other constraints in development and production environments. E.g.:

• Developer doesn't always know in advance what tool will be used in the future. Maybe there are commonly used tools that don't support some syntax.
• Code can be reusable if we can use it on different databases. This could be an argument to use one syntax over other.
• Maybe someone encountered a situation, where he wanted to have table creating statement without constraints defined in it and for what reason.

I believe that we could share our knowledge about when one syntax had actual benefits over other.

Answer 1

From an operational point of view: Give every constraint its own unique name, so you can change or remove the constraint later by referencing it by its name.

Additionally I prefer and normal index plus a named unique constraint to an unique index, so I can remove the constraint later without losing the index, when the DBMS supports this. Actually I have only seen this on Oracle: There you can back a unique constraint with an ordinary (non-unique) index. When dropping the constraint, the index stays. This can be very important for stable execution plans ("plan stability"). Unfortunately it looks like neither SQL Server nor PostgreSQL support this feature in their current versions.

With separately named constraints it is also easier to diff database schemas: You just enumerate all table names, column names, index names and constraint names sorted by name into a flat file. You can then easily compare this with a reference file and look for unexpected differences. This is much harder, when you don't have stable names and have to compare the semantics of the constraints.