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.
641
questions
0
votes
1
answer
43
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 ...
0
votes
1
answer
18
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 ...
0
votes
1
answer
41
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&...
0
votes
0
answers
60
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 ...
1
vote
2
answers
32
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, '-', ...
0
votes
1
answer
30
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 ...
0
votes
3
answers
199
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 ...
0
votes
1
answer
65
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 ...
0
votes
1
answer
55
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 ...
0
votes
1
answer
40
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 ...
0
votes
1
answer
88
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 ...
2
votes
0
answers
22
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
59
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 ...
1
vote
1
answer
111
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 ...
5
votes
1
answer
1k
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 ...
0
votes
0
answers
81
views
Can a derived attribute be used as (partial) key?
I am modelling a database in which a weak entity "A" has a derived attribute "Attr", and this attribute determines "A" uniquely with respect to its owner. Can I use it as ...
0
votes
1
answer
41
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 ...
1
vote
0
answers
106
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 ...
0
votes
1
answer
178
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 ...
1
vote
1
answer
72
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.
...
0
votes
1
answer
684
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, ...
0
votes
2
answers
146
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,
...
0
votes
2
answers
530
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
votes
1
answer
399
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
(
[...
-2
votes
1
answer
77
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 ...
0
votes
1
answer
387
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 ...
4
votes
1
answer
767
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 ...
1
vote
1
answer
121
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
...
7
votes
1
answer
179
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 (...
3
votes
1
answer
1k
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 ...
0
votes
1
answer
106
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 ...
-1
votes
1
answer
292
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,...
1
vote
1
answer
128
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 ...
1
vote
0
answers
159
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 (...
1
vote
1
answer
289
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
305
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 ...
0
votes
1
answer
49
views
Primary keys and sort columns in DolphinDB partitioned tables
I was trying to use non-time datatype as the sortColumns of partitioned table.
Suppose the data has 4 columns, namely
Time column : DateTime,
Two id columns : id_key, id_partition, where id_partition ...
1
vote
1
answer
253
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 ...
0
votes
0
answers
27
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, ...
0
votes
1
answer
1k
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 ...
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 ...
0
votes
0
answers
635
views
update primary key to a new one in sql server
I have got a table that has four columns and the Id is a Primary key:
Id
Matricule
Date
Code
G00183|2022-04-30|30
G00183
2022-04-30 00:00:00.000
30
G00183|2022-04-30|5010
G00183
2022-04-30 00:00:00....
0
votes
0
answers
787
views
Transactional Replication Error: Violation of Primary Key Constraint on value that doesn't exist at subscriber
I have transactional replication setup going to 3 subscribers. All 3 are randomly throwing the following error:
Violation of PRIMARY KEY constraint 'PK_RecieverList'. Cannot insert duplicate key in ...
1
vote
1
answer
3k
views
Unique constraint on partitioned table
PostgreSQL has a limitation on unique constraints on partitioned tables:
Unique constraints (and hence primary keys) on partitioned tables must include all the partition key columns. This limitation ...
0
votes
0
answers
96
views
How to prevent insertion of row which differs only in primary key from existing row?
If my table contains the following
+----+-----+------------+-----------+
| id | uid | date | operation |
+----+-----+------------+-----------+
| 1 | abc | 2022-09-21 | create |
| 2 | xyz | ...
3
votes
1
answer
591
views
If an entity's primary key must contain a foreign key, does that make the entity weak?
I'm currently learning about strong and weak entity types in the context of RDBMS. While reading, I came across this statement:
"A weak entity is dependent on a strong entity to ensure its ...
0
votes
0
answers
65
views
Postgres composite PK where one component is sequence different for each value of other field
Let's say I have a a_table with following fields:
id_a: bigint
id_b: int
some_third_value: text
I'd like to create a composite primary key on id_a and id_b so that id_a would be a sequence, but it ...
3
votes
2
answers
879
views
Why MySQL 8 doesn't return any rows when using a table with a descending PRIMARY KEY, 2 indexes, and valid join conditions with existing rows
Using 2 tables (A & B), joinable by their PK, with the PRIMARY KEY of A being sorted DESC (PK in descending order as it's allowed by MySQL 8), and A having 2 indexes in ASC ordering.
When I ...
0
votes
1
answer
469
views
faster INSERT INTO InnoDB table with composite PRIMARY KEY
I have a simple tag_map
CREATE TABLE tag_map
(
article_id int(11) unsigned NOT NULL,
tag_id int(11) unsigned NOT NULL,
INDEX(tag_id),
PRIMARY KEY(article_id,tag_id)
) ENGINE=InnoDB
The table is quite ...
2
votes
1
answer
900
views
How to get the "best of both worlds" with natural and surrogate keys? And could DBMSs be better?
I am designing my first database, and I find myself frustrated by the choice between storing an integer or a string for each instance of a categorical variable.
My understanding is that if I have a ...