What is the core differences between a table that is created with constraints like PK vs a table created or added indexes created on it only (that don't have PK) when it comes which route would anyone prefer to implement when creating a table? I have work on them but I am just curious to know what separate them Thank you
They are quite different.
Constraints check the data that is being inserted and updated meet some criteria (for example "not null"). If the data does not meet the criteria, the
UPDATE is rejected, and fails. Constraints help you to maintain the quality of the data.
Indexes improve (most of the time) the speed of a query, and are usually beneficial to
DELETE operations. Indexes improve database performance.
An index has no effect on how a query behaves nor the schema definition. It only effects performance. Although some SQL servers implement features using indexes, particularly the unique constraint. The SQL standard doesn't even mention them because they're considered an implementation issue.
A primary key constraint very much does have an effect on behavior and schema definition. It says this column must be unique and not null. Most databases also happen to index it for obvious performance reasons.
primary key rather than manually saying
unique not null also lets the person reading your schema know that this is the primary key. They will know what its purpose it. It also lets the database know this is the primary key which might allow it to do some extra optimizations.