Question 192566 in PhpMyAdmin Question posted in
The official documentation can be found here.

Phpmyadmin – How to Alter table with updating FOREIGN KEY

I am new in data base phpmyadmin
Here my query that is giving error

ALTER TABLE students
ADD CONSTRAINT fk_enrollment_student
FOREIGN KEY (student_id)
REFERENCES enrollments (student_id);

Here are structure of my table

Table: students
enter image description here

Table: enrollments
enter image description here

Table: courses
enter image description here
It is giving this error

SQL Query Console Console
ascendingdescendingOrder:Debug SQLExecution orderTime takenOrder by:Group queries
Some error occurred while getting SQL debug info.
OptionsSet default
Always expand query messages
Show query history at start
Show current browsing query
Execute queries on Enter and insert new line with Shift+Enter. To make this permanent, view settings.
Switch to dark theme
Storage engines
InnoDB Documentation
Supports transactions, row-level locking, foreign keys and encryption for tables

Variables | Buffer Pool | InnoDB Status ]

=====================================
2023-06-22 20:29:21 0x27b0 INNODB MONITOR OUTPUT

Per second averages calculated from the last 13 seconds

BACKGROUND THREAD

srv_master_thread loops: 15 srv_active, 0 srv_shutdown, 6417 srv_idle
srv_master_thread log flush and writes: 6432

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 41
OS WAIT ARRAY INFO: signal count 34
RW-shared spins 39, rounds 803, OS waits 26
RW-excl spins 2, rounds 1, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 20.59 RW-shared, 0.50 RW-excl, 0.00 RW-sx

LATEST FOREIGN KEY ERROR

2023-06-22 20:27:38 0x27b0 Error in foreign key constraint of table assignment1.students:

FOREIGN KEY (student_id)
REFERENCES enrollments (student_id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to https://mariadb.com/kb/en/library/foreign-keys/ for correct foreign key definition.
Alter table assignment1.students with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ‘
FOREIGN KEY (student_id)
REFERENCES enrollments (student_id)’.

TRANSACTIONS

Trx id counter 44
Purge done for trx’s n:o < 44 undo n:o < 0 state: running but idle
History list length 18
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 283841125138576, not started
0 lock struct(s), heap size 1128, 0 row lock(s)

FILE I/O

I/O thread 0 state: native aio handle (insert buffer thread)
I/O thread 1 state: native aio handle (log thread)
I/O thread 2 state: native aio handle (read thread)
I/O thread 3 state: native aio handle (read thread)
I/O thread 4 state: native aio handle (read thread)
I/O thread 5 state: native aio handle (read thread)
I/O thread 6 state: native aio handle (write thread)
I/O thread 7 state: native aio handle (write thread)
I/O thread 8 state: native aio handle (write thread)
I/O thread 9 state: native aio handle (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o’s:, sync i/o’s:
Pending flushes (fsync) log: 0; buffer pool: 0
181 OS file reads, 409 OS file writes, 136 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
0.00 hash searches/s, 3.92 non-hash searches/s

LOG

Log sequence number 75829
Log flushed up to 75829
Pages flushed up to 75829
Last checkpoint at 75820
0 pending log flushes, 0 pending chkp writes
90 log i/o’s done, 0.00 log i/o’s/second

BUFFER POOL AND MEMORY

Total large memory allocated 33554432
Dictionary memory allocated 30048
Buffer pool size 1003
Free buffers 747
Database pages 256
Old database pages 0
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 169, created 180, written 301
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 256, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=21176, Main thread ID=17500, state: sleeping
Number of rows inserted 10, updated 2, deleted 0, read 27
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

END OF INNODB MONITOR OUTPUT

How to solve this error ?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have relationship between students and enrollments backwards.

    ALTER TABLE enrollments
ADD CONSTRAINT fk_student_enrollment
FOREIGN KEY (student_id)
REFERENCES students (student_id);

  2. 0

    Your FK is on the wrong table. An FK references the PK on another table, so you want the FK on the enrolments table referencing the student table

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search