Questions tagged [primary-key]
In relational database design, a primary key can uniquely identify each row in a table. A primary key comprises a single column or a set of columns.
649 questions
0
votes
1
answer
80
views
Primary key constraint
I have few questions related to primary key in a table
I have a table with id as identity column and it is set as primary key
and also it created as clustured index by default
I want to remove the ...
user306367
0
votes
5
answers
81
views
Log table without primary key
I have a SQL Server log table that includes a column "Id"
This is an identity column, but not a primary key, it is not even indexed.
This would have just been set up from some tutorial for ...
- 1
3
votes
2
answers
470
views
Is there any benefit to declaring an index that contains a primary key as unique?
Suppose that I have a table with many columns that I don't care about, but two that I do: Primary and Secondary. There is a clustered primary key on Primary.
CREATE TABLE [dbo].[...
- 823
0
votes
0
answers
52
views
Is it a good idea to use time as primary key component?
I have a log/audit table which the only index I will ever use is time window.
Is it a good idea to PRIMARY KEY it as:
CREATE TABLE events (
created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(...
- 101
0
votes
0
answers
191
views
What is best way to change primary key of large table with minimal downtime?
Due to latch contention on large table with more than 1 billion records, I would like to reorder primary key definition from big int column to composite primary key. I know of two ways of doing this......
- 21
1
vote
1
answer
143
views
How to attach partition with primary key
I'm struggling to attach a partition to a table with a primary key.
I have a partitioned table Transactions:
create table "Transactions"
(
id bigserial ...
3
votes
2
answers
298
views
Why does MySQL generate a `key` statement in DDL for composite primary keys?
I am using MySQL version 8.3.0.
I can create a “many to many” relationship between persons and teams tables with the following SQL statements:
create table persons (
id bigint not null,
last_name ...
- 356
0
votes
1
answer
32
views
Create composed key (YEAR+COUNT) field with trigger after insert in SQLITE
I have a table Product with two columns: name of product and price
CREATE TABLE IF NOT EXISTS YearCounters (
year INTEGER PRIMARY KEY,
counter INTEGER DEFAULT 0
);
-- Product Table
CREATE ...
-2
votes
1
answer
897
views
How to remove violation of duplicate primary key in the table in sql server? [closed]
I am encountering a customer error as they are unable to print due to violation of primary key as being duplicate, below its where i went and check for the db column in the table and saw this as the ...
2
votes
2
answers
678
views
Design: What should be my PK, FK etc
I'm new to PostgreSQL and SQL in general and I've got a bit of an issue understanding PKs, FKs an serial id and when to use them. I'll try to add info on what I have already tried so far, but I can't ...
- 23
0
votes
0
answers
89
views
Implementing Surrogate Keys in Fact Tables
Can I just clarify a point: Primaries are still necessary to implement a Dimensional Model? As in, the duality of Surrogates and Primaries is incontrovertible?
The context is I am researching ...
0
votes
1
answer
109
views
trivial query very slow on primary key on a PostgreSQL DB (instantaneous on MySQL)
I have a table data:
with ~ 400 million rows
with data.id as int4, not null, and set as primary key
it's an aws RDS server, with ~128G of RAM
there are no row with id > 1e9
This query:
select ...
- 103
0
votes
1
answer
75
views
Alter all tables in schema to set an existing field as an IDENTITY PRIMARY KEY
Is there an easy and robust way to execute these two commands on all tables in a given schema myschema in a PostgreSQL (13) database:
ALTER TABLE myschema.table001 ADD PRIMARY KEY (oid);
ALTER TABLE ...
- 384
0
votes
1
answer
369
views
Should you include the primary key in an index, if the column is used in a condition?
Let's say I have this table:
create table "Sample"(
"id" bigserial primary key,
"source" varchar not null,
"category" int not null,
"t1&...
- 175
0
votes
0
answers
61
views
Do Primary Keys On Table Variables Have No Statistics At All? [duplicate]
My confusion is down to an apparent contradiction in principles. Primary keys are indexes and those always have statistics, but table variables notoriously always don't have statistics. What follows ...
- 823
1
vote
2
answers
55
views
Inserting a Hyphen in between a composite primary key
I have more of a conceptual question. Is there a way to model the following:
CREATE TABLE AssetPair (
older_asset_id INT8,
newer_asset_id INT8,
PRIMARY KEY (older_asset_id, '-', ...
- 13
0
votes
0
answers
43
views
General approach for creating updated records with identical primary keys while preserving existing records
I am working with a database which has some records in it already, and I can't modify the existing tables although I can create new tables.
I want to create new records which have the same primary ...
- 101
0
votes
3
answers
578
views
Why Do Primary Keys on Temp Tables Improve Performance Much More Than On Table Variables?
Many of my reports are essentially pivot tables. I take a set of big tables and transform each table individually so that a particular column (let's call it CaseID) that wasn't a primary key in the ...
- 823
0
votes
1
answer
86
views
SQL Server using table's Primary Key as part of Foreign Key
The situation is I have a Tenant table, and a tenant based status lookup table. Each with their own autonumber Primary keys.
Now I want to add a column to the Tenant table for a default Status that ...
- 15
0
votes
1
answer
250
views
Should I Use a Composite Key or a Single-Column Primary Key in my Entity Relationship Diagram?
I am designing an Entity Relationship Diagram (ERD) for a database that includes a table with variants annotated by different tools. In this table, each variant is annotated by a specific tool, and ...
- 101
0
votes
1
answer
52
views
Is there ever a reason to create a non-clustered index on a column that is already the clustered primary key?
Suppose that I have a table. Its primary key is clustered and consists of exactly one column. Is there ever a reason to also make a non-clustered index on that same column with no other columns in any ...
- 823
0
votes
1
answer
97
views
Is there any advantage in using IdentityColum+AnotherColumn vs just IdentityColum as the Clustered Index?
The database where I work has Table A with column A_id (an autoincrement field, which in practice is always unique, never null), and column Date (not unique), and other columns.
For some reason, the ...
- 121
2
votes
0
answers
25
views
Is there an algorithmic way to identify potential primary key foreign key relationships in postgres
I have a lots of tables in postgres with a lots of columns per each table. I need to write a program in such a way that it can identify the columns that can be a potential primary and foreign key pair....
0
votes
1
answer
95
views
How to add Primary key using new column in database
i converted my database from access to sql server ... now i want to replicate it and database is giving primary key error....i wrote below code to add a new column in all database but i am not able to ...
- 3
1
vote
1
answer
638
views
PostgreSQL: How to update a identity column to fix the error "duplicate key value violates unique constraint"
I have a table with the following structure:
CREATE TABLE categories (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name varchar NOT NULL
);
When I inserted some records with an explicit ...
- 111
6
votes
1
answer
3k
views
Adding a Primary Key to a Large PostgreSQL Table with High Traffic
I need to add a primary key to a large PostgreSQL table (approximately 2TB) with high traffic. This is a critical operation, and I'm looking for guidance on how to do it efficiently.
I have already ...
- 247
0
votes
1
answer
507
views
Create named PK when create table
I'm creating a table in Postgresql-11, and I need create a primary key for it at same time, to prevent from duplicated data being inserted before the PK created, so I want to these two operations be ...
- 359
1
vote
0
answers
170
views
Same update on different rows results in deadlocking
We are testing our SQL Server database for concurrency issues and it appears that a single, simple update statement being run concurrently in transactions is resulting in deadlocks.
Here is the ...
- 111
0
votes
1
answer
230
views
What are the use cases for MySQL Generated Invisible Primary Keys (GIPKs)?
I have read in the MySQL documentation that it is possible to enable the generation of invisible primary keys (https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html).
I'm quite a newbie in ...
- 3
1
vote
1
answer
164
views
insert trigger to auto-set key fields for composite primary key
Basically the next step for Error "UNIQUE constraint failed" with composite primary key:
I created a trigger after insert to set SEQ to the highest SEQ plus one if it's less than one.
...
- 125
0
votes
1
answer
1k
views
Error "UNIQUE constraint failed" with composite primary key
With littele SQL(ite) practice, I tried to create a table with a composite primary key:
CREATE TABLE NAMES (
TYPE INTEGER NOT NULL,
SEQ INTEGER NOT NULL,
NAME VARCHAR(20),
PRIMARY KEY (TYPE, ...
- 125
0
votes
2
answers
171
views
The fastest and most memory efficient way to reset PK column values for a table with over 5 mililon records
I have the following table schema:
CREATE TABLE [inputs].[source](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CreatedOn] [datetime] NULL,
[ImportedOn] [datetime] NULL,
[Identifier_Value] [nvarchar](25) NULL,
...
- 49
0
votes
2
answers
1k
views
Auto-increment text primary key in postgres
I would like my content to be searchable by users with a unique 6 characters id (i.e. zab4ac, cba884, ...).
I was thinking of creating a id character(6) primary key column, but then I started ...
- 3
3
votes
1
answer
416
views
Are individual indexes necessary on columns that are included in a compound primary key?
I have this M2M join table:
CREATE TABLE [dbo].[RecipientsDonors]
(
[RecipientId] [int] NOT NULL,
[DonorId] [int] NOT NULL,
CONSTRAINT [PK_RecipientsDonors] PRIMARY KEY CLUSTERED
(
[...
- 559
-2
votes
1
answer
129
views
Why making ISBNs primary keys of the book table a poor choice?
Why making ISBNs primary keys of the book table a poor choice? I don't quite get what Carter have explained in lecture 1 - Relating, CS50 SQL. Although ISBNs are memory consuming, but they are worth ...
- 11
1
vote
1
answer
604
views
Why can't my read-only user role see any of my tables' primary key information in psql?
I have a PostgreSQL database on Azure, and I've created a readonly_user role that allows for external parties to connect to the db in a read-only capacity using a connection string.
This readonly_user ...
- 113
4
votes
1
answer
1k
views
AWS Aurora Mysql seemingly not picking PRIMARY or secondary index
Relevant System Info:
Aurora Mysql 8.0.mysql_aurora.3.03
rg6.xl instances (1 writer, 2 read replicas)
Total size:5.5TB (all databases combined, or just looking at the most recent Snapshot)
I have been ...
- 135
1
vote
1
answer
177
views
Column containing keys from columns of another table
I have two csv files, countries and birth_rate_stat with the following data:
ISO
ISO3
ISO_CODE
FIPS
Display Name
Currency Name
Phone_Code
Region Code
AF
AFG
4
AF
Afghanistan
Afghani
93
Asia
AX
ALA
...
- 11
7
votes
1
answer
225
views
SQL Server Primary Key Column Statistics Histogram Suggests Duplicate Values
I have a statistic on a Primary Key column in a table. When I update the statistic with the default options:
UPDATE STATISTICS dbo.MyTable PK__MyTable__CB394B3946083350
I get a histogram as follows (...
- 2,083
3
votes
1
answer
3k
views
Add composite primary key on existing PostgreSQL table
I have a table on PostgresSQL with millions of rows. The table has columns a and b. I already have a unique index on a and b:
CONSTRAINT a_b UNIQUE (a, b)
I want to add a composite primary key on ...
- 133
0
votes
1
answer
144
views
MariaDB: Using composite primary key and index on the same columns gives strange results
Environment information:
Windows 11
10.6.8-MariaDB
In SO https://stackoverflow.com/questions/20055644/mysql-counter-within-group
I asked how can I add a counter within a group. It works quite well ...
- 257
-1
votes
1
answer
672
views
Best indexing strategy for composite primary key with frequently queried columns and occasional additional column [closed]
I have a large table with the following composite primary key:
PRIMARY KEY(col1, col2, col3)
col1, col2, col3 are queried all the time.
Some times (maybe 10% of the time) I get queries for col1, col2,...
- 99
1
vote
1
answer
194
views
PostgreSQL Logical Replication Trigger to add destination key column
I am using PostgreSQL Logical Replication to replicate data from multiple source databases into single one for analytical purposes.
Data from the source database have the same schema and therefore ...
- 170
1
vote
0
answers
197
views
How to export MySQL in a format accepted by DigitalOcean database clusters?
I am struggling with importing my databases to Digital Ocean database clusters. They have a setting that requires the primary key to be defined in the CREATE TABLE statement, but all my tools (...
- 141
1
vote
1
answer
514
views
Same primary key for multiple tables vs separate primary keys
Lets assume we have an employee table as below
employee
id (PK) | name | date_of_birth | age | created_at
We have a scenario where we need to add some metadata for each employee and not every employee ...
0
votes
1
answer
560
views
MariaDB/InnoDB performance: composite primary key vs. unused surrogate integer primary key to a table?
Years ago with MySQL/InnoDB, I've researched and found that adding a surrogate integer primary key to a table which doesn't have it always makes sense for performance reason. Even if that key is never ...
- 135
1
vote
1
answer
397
views
Set Default Value of Column Equal to PK of inserted record, and use it as Foreign Key Reference
I'm using MariaDB 10.3, and what I'm trying to do is an establishment of a 1-to-n relationship as for example specified here in the section "One-To-Many-Relationship". I'm trying to do so ...
- 163
0
votes
0
answers
33
views
Would INNER JOIN on equality of two keys delete rows effectively?
Ī̲ repair a MediaWiki site after a DoS attack which injected terabytes of garbage into the text table. Ī̲ found that DELETE with clauses works painfully slow and, even worse, locks the table; that is, ...
- 101
0
votes
1
answer
2k
views
best and quickest way to rename the primary key of a table
when I need to rename a table and consequently its primary key (and all other constraints) as well I use the following simple example script:
if object_id('dbo.Radhe1',N'U') is not null
drop table ...
- 16.6k
5
votes
3
answers
2k
views
Migrating existing tables to use INT primary key instead of UUID mysql v8
I'd love to hear everyone's opinion on this issue. I currently use Innodb mysql v8 in RDS. Our db is moderate, with ~130 tables, most are small, with our largest table having ~30 million rows over 6 ...
- 421