62
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 ...
- 29.3k
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 ...
- 28.9k
27
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 ...
- 371
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 ...
- 69.5k
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 ...
- 2,057
18
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 ...
- 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 ...
- 181
17
votes
Accepted
Add autoincrement to existing PK
The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an IDENTITY column,...
- 9,035
16
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-...
- 24.8k
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 ...
- 31.8k
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 ...
- 179k
14
votes
Accepted
How can I convert a key in a SQL Server deadlock report to the value?
The answers from @Kin, @AaronBertrand, and @DBAFromTheCold are great and were very helpful. One important piece of info I found during testing that the other answers left out is that you need to use ...
- 746
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 ...
- 82.1k
13
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 - ...
- 503
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, ...
- 29.3k
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 ...
- 24.6k
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 ...
- 1,053
13
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 ...
- 45.7k
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 ...
- 221
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 ...
- 68.1k
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 ...
- 38.2k
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 ...
- 69.6k
12
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 ...
- 170k
11
votes
Accepted
Is SYSUTCDATETIME a safe primary key?
( Please note that the original wording of the Question was: "Is SYSUTCDATETIME a safe clustered index?" )
What exactly do you mean by "safe"? A Clustered Index doesn't need to be ...
- 68.1k
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 ...
- 170k
10
votes
Accepted
Compacting a sequence in PostgreSQL
First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. Your life gets simpler if you just live with it. To get gap-less numbers, the (often better) ...
- 170k
10
votes
DynamoDB - Multiple range keys
Put a copy of B and C in the range field (as an extra column) and make that your range key like "B_C" and you can still also have separate columns for B and C if you need.
- 254
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 ...
- 333
10
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
...
- 1,919
Only top scored, non community-wiki answers of a minimum length are eligible