All Questions
Tagged with primary-key mysql
166 questions
3
votes
2
answers
299
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 ...
- 356
4
votes
1
answer
1k
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
177
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
-1
votes
1
answer
672
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
0
answers
197
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
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 ...
- 421
0
votes
0
answers
124
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
2
answers
966
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
533
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,531
0
votes
2
answers
56
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
2
votes
1
answer
759
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
2
votes
1
answer
3k
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, ...
- 31
0
votes
0
answers
129
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
2k
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 ...
- 101
0
votes
1
answer
1k
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, ...
- 299
0
votes
1
answer
73
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 ...
- 533
0
votes
1
answer
1k
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
2k
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
2
votes
1
answer
75
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
6k
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 ...
- 143
0
votes
1
answer
414
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 ...
- 123
-1
votes
1
answer
241
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
(
...
0
votes
1
answer
90
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 ...
- 385
0
votes
1
answer
2k
views
Am I breaking something if I set an autoincrement PK to 0?
I have this table
CREATE TABLE `test_table` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB;
...
- 113
0
votes
2
answers
950
views
In mysql, how to add a primary key and a composite key to a table so that it forbids duplicate rows, when there are null values within the columns?
In the following table (8 mi rows), every column has Null values. However, there can never be two identical rows (there will be always at least one column with a different value for each row).
How can ...
- 103
1
vote
1
answer
328
views
Understanding linking tables in a many-to-many relationship
I am still working on a photo storage app (designing the database for the data for the photos stored), using MySQL, and have been told that a photo may have one or more people in it, and a person may ...
- 29
0
votes
1
answer
400
views
MySQL id not saving in order (Laravel in production) [closed]
I'm really confused about this..
So, I created a Laravel app and hosted it on Heroku. I'm using ClearDB extension to be able to use MySQL. Problem is: when I save a new User on my DB, it is not being ...
1
vote
2
answers
1k
views
How can I get this code to work? It's in mysql, I keep getting an error 1822' failed to add foreign key constraint. Missing index for constraint' [closed]
CREATE TABLE EMPLOYEE (
employeeNumber VARCHAR (15) NOT NULL,
employeeName VARCHAR (20) NOT NULL,
department VARCHAR (10) NOT NULL,
phone INT NOT NULL,
PRIMARY KEY (employeeName, department)
...
- 13
3
votes
2
answers
1k
views
Hash IDs (such as base64 YouTube video Ids): store as int/bigint, or as string, and why?
I want to generate "unguessable" IDs, rather than /record/1, /record/2, /record/3 etc.
YouTube's style of using base 64 allows for 16.7 million IDs to be stored in just 4 characters (64 * 64 * 64 * ...
- 265
0
votes
1
answer
124
views
Optimise primary key and index settings for multiple link tables
I have this tables stated as below.
orders is the main table that will store all the details such as the customerID, merchantID, which riderID it was assign. The order details like total, subTotal, ...
- 207
0
votes
1
answer
2k
views
How can I use the same primary key across multiple tables as a foreign key? [closed]
I am working on a school project. I have a table of employees with a given employee ID. The employee ID on this table is the primary key. I have already set the employee ID as a foreign key on another ...
- 11
0
votes
0
answers
40
views
Should I use two indexes? [duplicate]
I would like to create some tables (including users table and products table) inside MySql Database with those index for each tables:
Private Index (uses int), for relational table purpose only
...
- 116
1
vote
1
answer
5k
views
Updating table basing on composite primary keys from derived table
I have two tables TableA and TableB defined as followings:
TableA has 4 columns id1 (Primary key), id2 (Primary key), code, enable.
TableB has 3 columns id (Primary key), code, loc.
TableB.code ...
1
vote
2
answers
4k
views
Primary Key Int vs String [duplicate]
I have an attributes table, where I want a unique name for each attribute in order to prevent confusion between potentially similar attributes.
In terms of the table design, the way I see it, there ...
1
vote
1
answer
70
views
Optimization on table selected where primary key
I have this query:
SELECT *
FROM wp_dev_posts, wp_dev_postmeta, wp_dev_term_relationships
WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'))
AND (wp_dev_posts.ID = ...
1
vote
1
answer
159
views
Can and should relational database contain tables that don't have primary keys? [duplicate]
So this site says: "All tables in a relational database should have a primary key". Here it also says that every table should have a primary key.
But if I look here and here they say that tables ...
- 111
1
vote
1
answer
4k
views
Insert into table with column same values as auto increment column ( computed column )
Say I have a table with "column1" (Primary Key, Auto Increment) and "column2".
How can insert into the table with column2 will have the same values as column1 which is auto increment column?
- 13
0
votes
1
answer
33
views
How do i get a list of records from two tables that have different values based on a matching PK? [closed]
Lets say i have the following two tables:
Table1:
|IP |Name | Gateway | Version |
+------+------+---------+---------+
|1 |A | 1.0.0 | v1 |
+------+------+---------+---------+
|2 ...
- 1
2
votes
2
answers
937
views
Predicting the primary keys exhaustion in MySQL based on rate of insertion
We are running out of primary keys for our table in production. We do not want that to occur again. So we need a mechanism to alert us when we are running out of primary keys based on the rate of ...
- 21
0
votes
2
answers
936
views
Query not returning results if selecting by indexed columns (Mysql 5.6)
We had a very strange encounter that all of our queries that uses an indexed column in the select wasn't returning any results. On the other hand, if we do select * or on any other non-indexed column ...
- 1
0
votes
1
answer
978
views
mysql - can a replica have a different primary key than the source?
I have a table with PRIMARY KEY (`id`) and I want to change it to PRIMARY KEY (`username`, `id`). These columns are defined as:
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`username` ...
- 21
0
votes
1
answer
636
views
Alter table - existing column as primary key
I am attempting to set one of the columns in the table as primary key.
Here are the columns from the table:
Now, when I run the sql:
ALTER TABLE cb_company
ADD PRIMARY KEY(uuid);
I get the ...
- 129
-1
votes
2
answers
1k
views
Finding MySQL Tables with no primary key OR no composite key
I can easily find tables that do not contain a PK from information_schema, but I'm having a tough time writing the logic to find tables that might contain neither a PK nor a Composite Keys (via two ...
- 109
2
votes
2
answers
2k
views
AdonisJS: is something wrong with belongsTo() relationship design?
In AdonisJs framework, suppose we have a Profile model in which we define belongsTo() relationship to theUser model. The documentation says this will lead us to define our databases this way:
The ...
- 135
1
vote
1
answer
67
views
When creating a many to many relation with an attribute, should I include a non-composite primary key?
For example, I have frame and voltage tables, both of which relate to a motor object:
frame(id, motor_attribute);
voltage(id, voltage_attribute);
I can pick a frame and pick a voltage and for each ...
- 133
1
vote
1
answer
316
views
How is it possible that a key of char+bigint has better performance than key of bigint+bigint?
I would like to store some user related data into a table. The data is identified by MD5 hash.
I have 2 options:
option 1 -
have a table with 2 columns: hash_id (unsigned bigint) and md5 (char(32))...
- 147
0
votes
1
answer
1k
views
Good practice for hiding primary keys from a client
Let's say I have a table like this:
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
Later on in my client, I may fetch a RESTful endpoint like this:
GET /v1/users/1
The ...
- 179
1
vote
1
answer
4k
views
mysql : ordering a table in the order of the primary key
I have a table with a primary key consisting of two integer columns.
I store ~200 millions lines in this table.
My program needs to get the full table ordered by the columns of the primary key in the ...
- 121
1
vote
1
answer
120
views
Best structure for MySQL table
I'm designing my table, but I have doubts: I will have two columns A and B that cannot have repetitions and, in the same row, can't both be null.
I don't know if the best thing to do is to use a ...
- 13
0
votes
1
answer
1k
views
MySQL Varchar Primary Key With Not Null Constraint Accepting Nulls
I have the following table definition:
CREATE TABLE `invite` (
`id` varchar(255) CHARACTER SET utf8 NOT NULL,
`email` varchar(255) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `...
- 1,093