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
0 votes
1 answer
13 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
34 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
66 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 ...
  • 133
0 votes
0 answers
17 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
0 votes
0 answers
30 views

Laravel > Session Table : How to speed up querying for the database sessions table?

Is there a reason laravel uses a varchar(191) column type for its sessions table id column? +----------------+------------+-------------+------------+---------+---------------+ | id | ...
  • 163
3 votes
2 answers
585 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
23 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,281
2 votes
1 answer
495 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 ...
0 votes
1 answer
25 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 ...
1 vote
0 answers
90 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 ...
0 votes
2 answers
45 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; +-------+------------+----------+--...
1 vote
0 answers
11 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. ...
  • 109
2 votes
1 answer
79 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 |...
0 votes
1 answer
49 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
14 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
1 vote
0 answers
106 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 ...
  • 119
0 votes
1 answer
32 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 ...
1 vote
2 answers
78 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
49 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
135 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?
0 votes
0 answers
32 views

Problem with column being forced as primary-key

I am new to postgresql and having some problem when creating a table using pgadmin gui. I define a column named id as integer primary-key and another column named idchat as integer not null. But, ...
1 vote
1 answer
411 views

Why do we need a Unique key when we have a Primary key?

I tried to Google this question but all I found were differences between primary and unique keys, why do we actually need a unique key when we have a primary key that can identify any single record, ...
1 vote
1 answer
117 views

Can a single parent table have more than one child table?

I am learning databases and sql. I am trying to understand if one single parent table can have multiple child tables, and if yes, just how common is it in financial companies? If this is allwoed, is ...
-2 votes
1 answer
15 views

How does one make a invoice database using a budget from a contract? [closed]

The primary key of an invoice table is not the same as the primary key of the budget table. The budget has the same exact invoice descriptions as the invoice table but there isn’t really a primary key ...
0 votes
1 answer
210 views

How do I create a primary composite key from three tables?

Suppose I have tableA with A.id, tableB with B.id and table C with C.id. Now I want to create a table D that has a primary composite key(A.id, B.id, C.id). How can I do this? create tableD( A.id ....
2 votes
1 answer
331 views

Having the same primary key as the parent table in one to one relationship

Building two tables as one-to-one relationship, I would like to consider which one is a better design ? Having the same primary key for the parent table (person) as a primary key also for the child ...
0 votes
0 answers
23 views

Can the primary key of a table with many columns be retroactively set in sqlite?

I have a table with hundreds of columns that I have imported from a csv file using sqlite3. One of the columns is called sid, and I would like this column to be the primary key of my table. Is this ...
0 votes
1 answer
646 views

How to add a serial ID to a table?

I have a table has more than 1000000 rows which was imported from CSV file. The table did not have a primary key, I want to add one with incremented bigint to the table. This is my table DDL: CREATE ...
  • 527
1 vote
3 answers
78 views

Is there a mechanism inside of database engines to avoid collisions of non-sequential keys? If so, how does it work?

Say that I create a table that that has a random uuid for a primary key. Is there typically a mechanism inside of the database to gracefully deal with collisions? How does it work? My mind is ...
0 votes
1 answer
172 views

Why is read performance using a covering index better than using a primary key?

I have this table with just Id and Value columns, and this is joined to to get the value in a common query. The Id is the (clustered) primary key and the value column is read in the select. If I add a ...
  • 3
2 votes
2 answers
511 views

Reduce the size of a primary key Index

I'm using Azure SQL DB and noticed that one of my largest databases appears to have a primary key index with a total size of 6Gb. It's a clustered index on a uniqueidentifier. However, the total rows ...
  • 123
0 votes
0 answers
62 views

MySQL: Migrate MyISAM table with multicolumn primary index with autoincrement to InnoDB

I have a large (10M rows) MyISAM table with a primary index on 2 different columns where one of them is auto_increment. How can I migrate it to InnoDB in MySQL 5.7? The table is something like: CREATE ...
0 votes
1 answer
629 views

MySQL 8 - Primary Keys - Sequential UUID or INT with AI?

My DB has ~100 tables and 3 for which I need UUIDs. For these 3 tables, the main goal is simply to hide sensitive IDs to visitors. MySQL 8 has introduced UUID support and I was planning for these 3 ...
0 votes
1 answer
261 views

Should I use composite key or primary key from other table

We have a Education Project which has following entities : Domain (e.g. Programming, UI/UX, AI, ML), each Domain has 5 Levels (1, 2, 3, 4, 5) Building Blocks - which are like small topics e.g. java, ...
  • 219
0 votes
1 answer
70 views

Dealing with a pair of nullable foreign keys which could "logically" be part of the identity

Maybe I'm going about this the wrong way, but here goes. A bit of background: I'm designing a system where users will select fields with a specified order. These fields are associated to a profile. ...
0 votes
1 answer
484 views

How can I have multiple foreign keys in one table? [duplicate]

I have a setup where I have accounts and teams. One account can belong to multiple teams and one team can belong to multiple accounts. How should I go about designing this in Postgres?
0 votes
0 answers
27 views

Database design: Deciding primary key for country, state, city tables [duplicate]

I have designed 3 tables as below: Table: Country Columns: country(name of country), iso2_code, iso3_code, phone_code primary key: country Table: State Columns: state(name of state), country(fk ...
0 votes
1 answer
33 views

Mimic AUTO_INCREMENT in MySQL

I've the following table: create table devies( id int not null, name varchar not null, groupId int not null, primary key(id), unique ux_name(name,groupId)); I get the data to this table and load it ...
  • 437
3 votes
2 answers
2k views

How to handle duplicates in an UPDATE on a primary key in Postgres?

Let's assume a scenario with the entities Person and Company and a table PersonCompanyStocks that models how many stocks a persons owns of a certain company (N:M cardinality). For example: person | ...
0 votes
1 answer
166 views

How to alter column to make it primary key when one already exists mysql8?

id | bigint unsigned | NO | PRI | NULL | auto_increment | | title | varchar(255) | NO | | NULL | | | category | varchar(255) | NO | | NULL |...
2 votes
2 answers
933 views

Why does MySQL Workbench let me add unique indexes to primary keys?

I'm using MySQL Workbench 8.0.12 Reproduce: Create a table Create the first column id, select it as a primary key (PK) Under indexes, PRIMARY appears Back under columns, also select id as unique (UQ) ...
  • 121
0 votes
0 answers
47 views

Can a database have an unnamed row?

I came across a question asking whether it is possible to have unnamed rows and columns in a database. I assumed unnamed implied primary key. I'm not sure whether my assumption is correct. When I ...
9 votes
2 answers
9k views

What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer) [duplicate]

I'm at a crossroads where I need to decide if I'm going to stick with bigserial as my primary key, or change to uuid (non-auto-generating—my API server will generate the ID using uuid v4 and insert it)...
  • 201
0 votes
1 answer
48 views

In SQLITE, how do you create a new table into its own file?

I am trying to do key value pairing, where performance is the top priority. I understand that the smaller a sqlite file is, the better the read and write speeds are, and so I am going to create about ...
0 votes
1 answer
199 views

Determining primary key columns in PGAdmin, if the constraint name is generated by system

I am trying to learn a new schema of a PostgreSQL database, but it's unclear from looking at pgAdmin which columns per the tables constitute the primary keys. It appears that when the tables were ...
  • 101
2 votes
1 answer
51 views

MySQL - Exclude a result if even one foreign key doesn't meet criteria (date availability checker)

I have three tables, one that lists regions, one that lists hotels and another that lists reservations. The hotels table has a foreign key for the regions table, to show which hotel is within which ...
  • 23
1 vote
1 answer
1k views

SQL: How to get all tables' columns & keys + the primary key?

Database Info MySQL 8.0.22 InnoDB The Goal I'm trying to get all tables in a database, with their column definitions and foreign keys, and primary keys on their own row for each table group. Given the ...
  • 133
1 vote
1 answer
825 views

MariaDB Uuid as Primary Key Performance Problem / Tweaks

im upgrading our mariadb database from PRIMARY KEY INT AUTO_INCREMENT to UUID PRIMARY KEY. We have around 80 tables with multiple relations between them. I've build a migration script to change the id ...
0 votes
1 answer
222 views

Innodb primary key advice

We have a database table growing at a rate of ~20 million records per month, we're expecting this to double shortly. The existing schema has a uuid4 as a primary key, and then two additional indexes ...
-2 votes
2 answers
68 views

PostgreSQL read performance is terrible [closed]

Table id, description situation Contains 5 records id is PK request SELECT * FROM table WHERE id = 1 Execution 743 ms Question Why in heavens name is this so slow. Same design, samen query on MySQL ...
  • 1

1
2 3 4 5
13