CODEWITHSUNDEEP
sqlprimary-keytheory
MercilessMaverick
MercilessMaverick

Reputation: 257

SQL: Where should the Primary Key be defined

I'm creating a database with several sql files 1 file creates the tables. 1 file adds constraints. 1 file drops constraints.

The primary is a constraint however I've been told by someone to define your primary key in the table definition but not given a reason why.

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

My current thinking is to do it in the table definition because doing it as a removable constraint could potentially lead to some horrible issues with duplicate keys. But dropping constraints could lead to serious issues anyway so it is expected that if someone did drop the primary key, they would have taken appropriate steps to avoid problems as they should have for any other data entry

Upvotes: 0

Views: 459

Answers (5)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

There's a certain logical sense in keeping everything related to a table in one file--column definitions, keys, indexes, triggers, etc. If you never have to rebuild a very large database from SQL, that will work fine almost all the time. The few times it doesn't work well probably aren't worth changing the process of keeping all the related things together in one file.

But if you have to rebuild a very large database, or if you need to move a database onto a different server for testing, or if you just want to fiddle around with things, it makes sense to split things up. In PostgreSQL, we break things up like this. All these files are under version control.

  • All CREATE DOMAIN statements in one file.
  • Each CREATE TABLE statement in a separate file. That file includes all constraints except FOREIGN KEY constraints, expressed as ALTER TABLE statements. (More about this in a bit.)
  • Each table's FOREIGN KEY constraints in a separate file.
  • Each table's indexes for non-key columns in a separate file.
  • Each table's triggers in a separate file. (If a table has three triggers, all three go in one file.)
  • Each table's data in a separate file. (Only for tables loaded before bringing the database online.)
  • Each table's rules in a separate file.
  • Each function in a separate file. (Functions are PostgreSQL's equivalent to stored procedures.)

Without foreign key constraints, we can load tables in any order. After the tables are loaded, we can run a single script to rebuild all the foreign keys. The makefile takes care of bundling the right individual files together. (Since they're separate files, we can run them individually if we want to.)

Tables load faster if they don't have constraints. I said we put each CREATE TABLE statement in a separate file. The file includes all constraints except FOREIGN KEY constraints, expressed as ALTER TABLE statements. You can use the streaming editor sed to split those files into two pieces. One piece has the column definitions; the other piece has all the 'ALTER TABLE ADD CONSTRAINT' statements. The makefile takes care of splitting the source files and bundling them together--all the table definitions in one SQL file, and all the ALTER TABLE statements in another. Then we can run a single script to create all the tables, load the tables, then run a single script to rebuild all the constraints.

make is your friend.

Upvotes: 0

nvogel
nvogel

Reputation: 25526

For effective source control it usually makes sense to have a separate script for each object (constraints included). That way you can track changes to each object individually.

Upvotes: 0

Neville Kuyt
Neville Kuyt

Reputation: 29619

All of this is somewhat platform specific, but a primary key is a logical concept, whereas a constraint (or unique index, or whatever) is a physical thing that implements the logical concept of "primary key". That's another reason to argue for putting it with the table itself - it's logical home - rather than the constraints file.

Upvotes: 0

Marc B
Marc B

Reputation: 360592

A primary key is a constraint, but a constraint is not necessarily a primary key. Short of doing some major database surgery, there should never be a need to drop a primary key, ever.

Defining the primary key along with the table is good practice - if you separate the table and the key definition, that opens the window to the key definition getting lost or forgotten. Given that any decent database design utterly depends on consistent keys, you don't ever want to have even the slightest chance that your primary keys aren't functioning properly.

Upvotes: 3

Victor Parmar
Victor Parmar

Reputation: 5779

From a maintainability perspective I would say that it is better to have the Primary Key in the table definition as it is a very good indicator of what the table will most likely be used for.

The other constraints are important as well though and your argument holds.

Upvotes: 0

Related Questions