Skip to main content
64 votes
Accepted

Are composite primary keys bad practice?

To say that the use of "Composite keys as PRIMARY KEY is bad practice" is utter nonsense! Composite PRIMARY KEYs are often a very "good thing" and the only way to model natural ...
Vérace's user avatar
  • 30.2k
28 votes

Guid vs INT - Which is better as a primary key?

Using auto increment IDs might leak information about your business activity. If you are running a shop and uses order_id to publicly identify a purchase, then anybody can find out your monthly number ...
golopot's user avatar
  • 381
28 votes

How is this SQL Server PK violation possible?

Now I have a SQL statement, which selects the MAX value, adds to it, and INSERTs that value into the same table -- all inside one statement. So in theory, it's not possible to get a PK violation on ...
Sean Gallardy's user avatar
26 votes
Accepted

Why would a primary key value change?

If you were using a person's name as a primary key and their name changed you would need to change the primary key. This is what ON UPDATE CASCADE is used for since it essentially cascades the change ...
Hannah Vernon's user avatar
  • 70.2k
22 votes

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

As per the documentation, the precision of the CURRENT_TIMESTAMP is microseconds. Thus, the probability of a collision is low, but possible. Now imagine a bug which happens very rarely, and causes ...
peterh's user avatar
  • 2,109
19 votes

Can a database table not have a primary key?

Can you create a database table without a primary key? Well, you just said you can in SQLite. And, I believe that holds true for almost every (if not every) major DBMS platform. Should you create a ...
RDFozz's user avatar
  • 11.6k
17 votes

Why does Postgres generate an already used PK value?

You are most likely tying to insert a row in a table for which the serial column sequence value is not updated. Consider following column in your table which is primary key defined by Django ORM for ...
Abhishek's user avatar
  • 181
17 votes
Accepted

Does ordering by auto-incrementing PK ensure chronological order?

Assuming by "auto-incrementing" you mean the Postgres SERIAL pseudo-type, the short answer is "not always". SERIAL columns are implemented using standard SQL sequences, which might generate out-of-...
mustaccio's user avatar
  • 26.3k
15 votes
Accepted

Ordering of table is based on Clustered index or non clustered primary key?

Unless you explicitly state a desired order using an ORDER BY clause you can not guarantee the order that data will be presented in response to a query. Without an ORDER BY clause the engine is free ...
David Spillett's user avatar
15 votes
Accepted

Checking @@ROWCOUNT failing

I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting ...
Aaron Bertrand's user avatar
14 votes

Retrieving all PK and FK

No need to parse pg_get_constraintdef(), just use columns of pg_constraint table to obtain other details (the docs). Here constraint_type can be: p - primary key, f - foreign key, u - unique, c - ...
Evgeny Nozdrev's user avatar
14 votes
Accepted

Identity Column in Concurrency, Multithreading, Parallel Processing

Would the identity primary key column fail, with a primary key violation in any way, example: processors are trying to input the same identity number? This is covered in the SQL Server product ...
Paul White's user avatar
  • 86.9k
14 votes
Accepted

What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer)

It is easy enough to benchmark this, but the INSERT performance of UUIDs will be worse, because they are bigger and slower to generate. But it doesn't sound like you are building a high performance ...
Laurenz Albe's user avatar
  • 53.8k
14 votes
Accepted

Adding a Primary Key to a Large PostgreSQL Table with High Traffic

Why? Your step 1 would need a lot more than 600 GB (temporarily). The table has around 2 TB. About as much (minus possible bloat, plus 8 bytes per row for the new bigint column) has to be available at ...
Erwin Brandstetter's user avatar
13 votes
Accepted

What are the best practices regarding lookup tables in relational databases?

By IDN, I take it you mean an IDENTITY, SEQUENCE or AUTO_INCREMENT field? You should take a look here and here. Note, section 5 (Misusing Data values as Data Elements) of the first reference, ...
Vérace's user avatar
  • 30.2k
13 votes

What are the best practices regarding lookup tables in relational databases?

There is a third approach which has some of the advantages of your two options - put an actual code in the code table. By this I mean a short character sequence that captures the essence of the full ...
Michael Green's user avatar
13 votes
Accepted

How is this SQL Server PK violation possible?

Your theory is wrong. Reading from your "identity" table doesn't lock it, the insert into it locks it. While they are part of a single compound statement, they are still seperate actions. Two (or ...
jmoreno's user avatar
  • 1,065
12 votes

Retrieving all PK and FK

Based on Erwin solution: SELECT conrelid::regclass AS "FK_Table" ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), 14, position(')' in ...
profimedica's user avatar
12 votes

Should I add an auto-increment / IDENTITY field to a cross-reference table just for PK purposes?

One thing to consider is that a Primary Key and a Clustered Index are not the same thing. A Primary Key is a constraint and deals with the rules by which the data lives (i.e. data integrity); it has ...
Solomon Rutzky's user avatar
12 votes
Accepted

Should I avoid adding a clustered key to a table with high throughput

The main reason I would want a clustered index in this scenario is this line: The process for deleting process.Audit records will execute every hour and delete an hours worth of audits from x days ...
Erik Darling's user avatar
12 votes

Choosing primary keys: Scientific names of species or system-assigned numeric identifiers?

I would use my own identifier. The species name may be unique but - it's too long - it's a string For example, in SQL Server, if used as the clustered primary key it will be used in non-clustered ...
gbn's user avatar
  • 70k
12 votes
Accepted

Is there a performance benefit to use tinyint instead of int for Primary Key?

Yes, there is a performance benefit to using TINYINT vs INT. Design your tables to minimize their space on the disk. This can result in huge improvements by reducing the amount of data written to ...
Willem Renzema's user avatar
12 votes
Accepted

PostgreSQL - How to list all foreign key link to Primary key of a table

You can query the catalog tables: SELECT la.attrelid::regclass AS referencing_table, la.attname AS referencing_column FROM pg_constraint AS c JOIN pg_index AS i ON i.indexrelid = c....
Laurenz Albe's user avatar
  • 53.8k
11 votes
Accepted

UUID or BIGSERIAL Foreign Keys Postgres

Use uuid if you need it. Don't use it if you don't. bigint is smaller and a bit faster in multiple ways. 8 vs. 16 bytes do not sound like much, but the difference stacks up for big tables and multiple ...
Erwin Brandstetter's user avatar
10 votes

Multiple primary keys in PostgreSQL

You can define a table like this: CREATE TABLE mytable ( field1 INTEGER, field2 INTEGER, PRIMARY KEY (field1, field2) );
Demitri's user avatar
  • 221
10 votes
Accepted

Choosing primary keys: Scientific names of species or system-assigned numeric identifiers?

As a former environmental scientist with a bit of "bugs and bunnies" background (fish and inverts specifically), I would recommend the use your own identifier. As a database administrator, you have ...
Zerodf's user avatar
  • 333
10 votes

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

Not really because it’s possible for CURRENT_TIMESTAMP to provide two identical values for two subsequent INSERTs (or a single INSERT with multiple rows). Use a time-based UUID instead: ...
Linas Valiukas's user avatar
10 votes

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

Strictly speaking: No. Because CURRENT_TIMESTAMP is a function and only one or more table columns can form a PRIMARY KEY constraint. If you mean to create a PRIMARY KEY constraint on a column with ...
Erwin Brandstetter's user avatar
9 votes

Guid vs INT - Which is better as a primary key?

I see a whole lot of typical answers on this thread about Random GUIDs and things like NEWSEQUENTIALID() saving the day. Yes, I do agree GUIDs in any form are quite large but that's really the only ...
Jeff Moden's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible