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.
628
questions
0
votes
1
answer
47
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
34
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
5
votes
1
answer
894
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 ...
- 213
0
votes
0
answers
50
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
30
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 ...
- 315
1
vote
0
answers
83
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 ...
- 11
0
votes
1
answer
125
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
39
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
385
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
129
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
334
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
396
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
(
[...
- 549
-3
votes
1
answer
63
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 ...
- 1
0
votes
1
answer
257
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 ...
- 103
4
votes
1
answer
608
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
99
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
155
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 (...
- 1,928
2
votes
1
answer
721
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 ...
- 123
0
votes
1
answer
90
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
154
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
98
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
141
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
232
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
241
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
0
votes
1
answer
43
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
199
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
26
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
823
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 ...
- 15.8k
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 ...
- 411
0
votes
0
answers
591
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
707
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 ...
0
votes
1
answer
2k
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 ...
- 465
0
votes
0
answers
86
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 | ...
- 103
3
votes
1
answer
510
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 ...
- 141
0
votes
0
answers
56
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 ...
- 101
3
votes
2
answers
835
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 ...
- 209
0
votes
1
answer
429
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 ...
- 4,491
2
votes
1
answer
835
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 ...
- 125
0
votes
1
answer
247
views
How to automaticaly create entries in a parent table before filling child table? What is that called in db terminology?
What I want is for inserting the string name of a publication in a child table to associate with the PK of the publication in the publications table. For example, let's say I have a table of counties ...
- 125
1
vote
0
answers
364
views
Why would executing individual update statements on two unrelated tables each within their own transaction cause deadlock?
I've searched the stackoverflow network, but couldn't find anything similar to the scenario detailed below. It doesn't make sense how deadlock could occur, but we're seeing logs from the database ...
- 25
0
votes
2
answers
51
views
Why does MariaDB create another key for this column when it is already part of the primary key?
create table foo (
id int primary key
);
create table bar (
a int references foo(id),
b int references foo(id),
primary key (a, b)
);
show index from bar;
+-------+------------+----------+--...
- 113
1
vote
0
answers
13
views
Primary key design - autoincrement vs composite vs artificial key for time-series data [duplicate]
I have a non-relational table that records visits per area across time (months):
area_id
period
visitors
visits
91387821
2022-04
452
664
91387821
2022-05
516
704
105252924
2022-04
8834
20445
etc.
...
- 111
2
votes
1
answer
597
views
Mysql - Table structure to hold history log without any primary key
I have mysql table that holds log data for some products, this data are deleted and inserted by a service every 5 minutes.
This is the table:
| id | product_id | dev_stage_id | production_order | log |...
- 121
0
votes
1
answer
134
views
Should I be using a non-clustered primary key on multiple columns?
I have a very large table that will just continue to get larger. It contains hourly interval meter usage data for around 22,000 meters.
So, everyday 22,000 * 24 hours = 528,000 records are created. ...
- 1
0
votes
0
answers
20
views
Should I create a linking table between two tables with existing IDs or add a column to an existing table
So I have two tables. Not with these exact columns but it's just to illustrate the point
users
id | forename | surname
forms
id | name
Using MS SQL and was wondering what's best practice to link a ...
- 194
2
votes
0
answers
742
views
Postgresql 14 Primary Key not working, allows duplicates
I discovered that my 3-column (integers, not null) primary key is allowing duplicates!
Then I created new unique index and replace the current one, and the problem still exists, how is that even ...
- 129
0
votes
1
answer
33
views
Why would an "articles" table (with no relationship to any other table) have a primary key?
Let's say that I have an articles table that have two columns: title and content, and let's say that this articles table don't have any relationship with any other table.
Why would this articles table ...
- 123
1
vote
2
answers
184
views
Separate table to store long string identifiers
In my postgres DB, have an assets and a sales table. The current owner of an asset is identified by an Ethereum address with 42 characters, e.g. 0x81238432CE9b51Fbc2b59AFD74Ba8D09863B8B00. A sale has ...
- 111
0
votes
1
answer
466
views
SQL Server- can a select query be executed to specify column names for the insert into command?
So I'm trying to insert the same table into itself, but I have got a breakage because of repetition of primary keys. Hence, I'm thinking to insert the same rows without the primary key first, and then ...
1
vote
0
answers
405
views
How can I share virtual key in DBeaver for other device?
As I know, the virtual key in DBeaver just existed on the client-side (local device), not in real database. I want to share this virtual key for other devices (not only my device). How can I do that?
- 11