SQL: where should the primary key be defined

Question

I create a database with several sql files 1 creates tables. 1 file adds restrictions. 1 file resets restrictions.

Primary is a limitation, however, someone told me to determine your primary key in the table definition, but did not give a reason.

Is it better to define the primary key as a constraint that can be added and dropped, or is it better to do so in the table definition.

My real thinking is to do this in a table definition, because doing this as a plugin constraint could potentially lead to some terrible problems with duplicate keys. But failure restrictions can lead to serious problems in any case, so it is expected that if someone resets the primary key, they will take appropriate steps to avoid the problems they should have for any other data record.

Answer 1

A primary key is a constraint, but a constraint is not necessarily a primary key. If you do not perform a major database operation, you will never have to give up the primary key.

Defining a primary key along with a table is a good practice - if you separate the table and the key definition, this will open a window for determining the key that will be lost or forgotten. Given that any decent database design is completely dependent on agreed keys, you never want to have even the slightest chance that your primary keys will not work properly.

Answer 2

From the point of view of maintainability, I would say that it is better to have a primary key in the definition of the table, since it is a very good indicator of what the table is most likely to be used for.

Other restrictions are also important, and your argument remains.

Answer 3

All this is somewhat platform-specific, but the primary key is a logical concept, while a constraint (or a unique index or something else) is a physical thing that implements the logical concept of a “primary key”. This is another reason to argue about putting it with the table itself - it is a logical house, not a file of restrictions.

Answer 4

For effective source control, it usually makes sense to have a separate script for each object (including restrictions). Thus, you can track changes for each object individually.

Answer 5

There is a certain logical sense in saving everything that is connected with a table in one file - column definitions, keys, indexes, triggers, etc. If you never have to rebuild a very large database from SQL, this will work fine for almost time. This doesn’t work well several times, probably it’s not worth changing the process of storing all related things in one file.

But if you need to rebuild a very large database or if you need to move the database to another server for testing, or if you just want to mess with things, it makes sense to split things up. In PostgreSQL, we break that down. All of these files are under version control.

• All CREATE DOMAIN statements in one file.
• Each CREATE TABLE statement in a separate file. This file contains all restrictions except the FOREIGN KEY restrictions expressed in ALTER TABLE expressions. (More on this a bit.)
• Each FOREIGN KEY constraint table in a separate file.
• Each table indexes non-key columns in a separate file.
• Each table is launched in a separate file. (If there are three triggers in the table, all three go to the same file.)
• Each data table in a separate file. (Only for tables uploaded before bringing the database to the network.)
• Each table is managed in a separate file.
• Each function in a separate file. (PostgreSQL functions are equivalent to stored procedures.)

Without foreign key restrictions, we can load tables in any order. After loading the tables, we can run one script to rebuild all the foreign keys. The Makefile takes care of combining the right individual files together. (Since they are separate files, we can run them individually if we want.)

Tables load faster if they have no restrictions. I said that we put each CREATE TABLE statement in a separate file. The file contains all the restrictions except the FOREIGN KEY constraints expressed in ALTER TABLE expressions. You can use sed stream editor to split these files into two parts. One part has column definitions; the other part has all the ALTER TABLE ADD CONSTRAINT instructions. The Makefile takes care of splitting the source files and combining them - all table definitions in one SQL file and all ALTER TABLE statements in another. Then we can run one script to create all the tables, load the tables, and then run one script to rebuild all the restrictions.

make is your friend.