Skip to main content

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.

Filter by
Sorted by
Tagged with
3 votes
2 answers
258 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 ...
FloT's user avatar
  • 356
0 votes
1 answer
25 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 ...
kiaderouiche's user avatar
-2 votes
1 answer
148 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 ...
thrue2023's user avatar
2 votes
2 answers
606 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 ...
JUHJUH's user avatar
  • 23
0 votes
0 answers
28 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 ...
Geoffrey Turner's user avatar
0 votes
1 answer
52 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 ...
Karl Forner's user avatar
0 votes
1 answer
27 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 ...
s.k's user avatar
  • 372
0 votes
1 answer
63 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&...
birgersp's user avatar
  • 175
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 ...
J. Mini's user avatar
  • 477
1 vote
2 answers
38 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, '-', ...
Eric Sharma's user avatar
0 votes
0 answers
38 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 ...
Robert Dodier's user avatar
0 votes
3 answers
290 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 ...
J. Mini's user avatar
  • 477
0 votes
1 answer
74 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 ...
mcsov's user avatar
  • 15
0 votes
1 answer
106 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 ...
Cristian Riccio's user avatar
0 votes
1 answer
43 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 ...
J. Mini's user avatar
  • 477
0 votes
1 answer
91 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 ...
Juan Perez's user avatar
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....
Ashwin Prasad's user avatar
0 votes
1 answer
65 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 ...
omi4u's user avatar
  • 3
1 vote
1 answer
297 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 ...
Jaime Suncin's user avatar
6 votes
1 answer
2k 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 ...
sujeet's user avatar
  • 245
0 votes
0 answers
96 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 ...
Gabriel Franceschi Libardi's user avatar
0 votes
1 answer
150 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 ...
Leon's user avatar
  • 347
1 vote
0 answers
127 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 ...
theta-fish's user avatar
0 votes
1 answer
193 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 ...
Alex's user avatar
  • 3
1 vote
1 answer
102 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. ...
U. Windl's user avatar
  • 125
0 votes
1 answer
854 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, ...
U. Windl's user avatar
  • 125
0 votes
2 answers
153 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, ...
Gleb Lashuk's user avatar
0 votes
2 answers
818 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 ...
pascal's user avatar
  • 3
3 votes
1 answer
402 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 ( [...
InteXX's user avatar
  • 559
-2 votes
1 answer
106 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 ...
Đạt Phạm's user avatar
1 vote
1 answer
469 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 ...
justadampaul's user avatar
4 votes
1 answer
960 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 ...
Randoneering's user avatar
1 vote
1 answer
151 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 ...
Red Tornado's user avatar
7 votes
1 answer
193 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 (...
SE1986's user avatar
  • 1,990
3 votes
1 answer
2k 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 ...
gkatzioura's user avatar
0 votes
1 answer
115 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 ...
giordano's user avatar
  • 257
-1 votes
1 answer
412 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,...
Kevin's user avatar
  • 99
1 vote
1 answer
149 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 ...
Stavros Koureas's user avatar
1 vote
0 answers
184 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 (...
Matt Welander's user avatar
1 vote
1 answer
351 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 ...
Dushyant Sabharwal's user avatar
0 votes
1 answer
385 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 ...
Collector's user avatar
  • 135
1 vote
1 answer
307 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 ...
DevelJoe's user avatar
  • 163
0 votes
0 answers
28 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, ...
Incnis Mrsi's user avatar
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 ...
Marcello Miorelli's user avatar
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 ...
Muab Nhoj's user avatar
  • 411
0 votes
0 answers
694 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....
XMehdi01's user avatar
  • 302
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 ...
Gunther Schadow's user avatar
0 votes
0 answers
101 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 | ...
loris's user avatar
  • 103
3 votes
1 answer
711 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 ...
Izzo's user avatar
  • 141
0 votes
0 answers
79 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 ...
Tarmo's user avatar
  • 101

1
2 3 4 5
13