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
-1 votes
0 answers
21 views

Finding all minimal keys

I am a little bit confused regarding the next: To find a candidate to find a minimal key, the attribute don't need to occur in the right-hand side. But now I am struggling with this one, because all ...
user avatar
  • 1
0 votes
1 answer
39 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. ...
user avatar
  • 1
0 votes
0 answers
11 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 ...
user avatar
  • 194
0 votes
0 answers
28 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 ...
user avatar
  • 109
0 votes
1 answer
29 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 ...
user avatar
1 vote
2 answers
39 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 ...
user avatar
  • 111
0 votes
1 answer
45 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 ...
user avatar
0 votes
0 answers
51 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?
user avatar
0 votes
0 answers
27 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, ...
user avatar
1 vote
1 answer
83 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, ...
user avatar
1 vote
1 answer
41 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 ...
user avatar
-2 votes
1 answer
13 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 ...
user avatar
0 votes
1 answer
40 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 ....
user avatar
2 votes
1 answer
120 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 ...
user avatar
0 votes
0 answers
16 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 ...
user avatar
0 votes
1 answer
143 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 ...
user avatar
  • 477
1 vote
3 answers
62 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 ...
user avatar
0 votes
1 answer
135 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 ...
user avatar
  • 3
2 votes
2 answers
416 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 ...
user avatar
  • 123
0 votes
0 answers
45 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 ...
user avatar
0 votes
1 answer
434 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 ...
user avatar
0 votes
1 answer
72 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, ...
user avatar
  • 209
0 votes
1 answer
48 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. ...
user avatar
0 votes
1 answer
202 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?
user avatar
0 votes
0 answers
26 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 ...
user avatar
0 votes
1 answer
28 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 ...
user avatar
  • 373
0 votes
0 answers
42 views

Add a foreign key to a composite primary key on a UNF database

Working on an over 30 y.o. UNF Oracle 8i database (which has lately been migrated to 19c). Here a summarized description of the 3 weirdly linked tables: Item, Category, SubCategory. create table Item (...
user avatar
  • 111
3 votes
2 answers
1k 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 | ...
user avatar
0 votes
1 answer
99 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 |...
user avatar
2 votes
2 answers
665 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) ...
user avatar
  • 121
0 votes
0 answers
44 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 ...
user avatar
7 votes
2 answers
6k 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)...
user avatar
  • 181
0 votes
1 answer
37 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 ...
user avatar
0 votes
1 answer
113 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 ...
user avatar
  • 1
2 votes
1 answer
41 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 ...
user avatar
  • 23
1 vote
1 answer
899 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 ...
user avatar
  • 133
1 vote
1 answer
666 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 ...
user avatar
0 votes
1 answer
170 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 ...
user avatar
-2 votes
2 answers
47 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 ...
user avatar
  • 1
1 vote
1 answer
37 views

Are indexes need for parts of primary key?

In Postgres, is it beneficial to query performance to create an index on field2 in the following table if I intend to sometimes use queries with only field2 in the WHERE clause? CREATE TABLE my_table (...
user avatar
  • 143
1 vote
2 answers
518 views

How to allow NULL in foreign keys of a compound primary key

I want to have NULLs in foreign keys of a compound primary key. This is an example of what I would expect to be valid data. product_id variant_id 123-123 ABC 123-123 NULL 456-456 ABC I cannot ...
user avatar
  • 23
-1 votes
1 answer
140 views

Error 1005 errno 150 "Foreign key constraint is incorrectly formed" where no foreign key declared

I have an issue on a basic CREATE TABLE statement Error 1005 errno 150 "Foreign key constraint is incorrectly formed" returned where no Foreign key is declared - CREATE TABLE Person ( ...
user avatar
1 vote
2 answers
288 views

Does it matter to convert integer primary key into equivalent string?

In a PostgreSQL DB, I have a sortable bigint primary key, which is a bit verbose in terms of readability. I want to encode it as base36 or base64, like using char(n). In theory the number of bytes ...
user avatar
0 votes
1 answer
140 views

Should we have OrderId and separate OrderNumber for E-Commerce Website or we can have only field for both?

I am working on an Online Grocery Store type of website in which I have one order table to store the order details. Here I am having confusion that whether I should have an OrderId (primary key) and ...
user avatar
  • 133
2 votes
1 answer
41 views

Best practices for integer key types - mixed or all the same integer type?

I am designing a postgres database for scientific data. It has about 30-40 tables, many of which are lookup tables with less than 10 records. The bulk of the data are concentrated in just a few tables ...
user avatar
  • 143
-1 votes
3 answers
471 views

Advantages / disadvantages of simple vs compound primary keys

I am trying to model for a database. For one part of the problem I have an entity Area, which contains Departments, and each department is assigned specific functions. I have the following. I have ...
user avatar
0 votes
1 answer
491 views

Create fast-refresh MV over dblink on table without PK?

I want to create a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c) via a dblink. The MV would refresh on a 30-second schedule (since on-commit is not ...
user avatar
  • 1,373
1 vote
1 answer
103 views

Why am I able to create MV logs using PK — even though there is no PK?

I have a table that does not have a primary key: I'm able to create MV logs on the table — without specifying whether to use the primary key or the rowid: create materialized view log on maximo....
user avatar
  • 1,373
0 votes
1 answer
45 views

Postgres: find all FK constraints associated with a given PK

Given some input PK column, e.g. ani.animals.id, I want to find all FK constraints names associated with that PK. I also need table and schema's names where each particular FK constraint is located. ...
user avatar
  • 115
0 votes
1 answer
22 views

How to design table to allow marking records as related to each other?

My web app's main MySQL table has tens of thousands of rows. Pretend it has 2 columns: ID, Content. I'm trying to design a new feature that allows rows to be marked as related to each other. Obviously ...
user avatar
  • 295

1
2 3 4 5
12