Geek asks and answers

Can MySql calmly refuse inserts when a foreign key fails?

I execute INSERT INTO Foo VALUES (a, b, c, ...), and sometimes one of a, betc. does not satisfy the foreign key constraint.

In this situation, I want the insertion not to run and not generate an error.

Can I do this in one expression, or do I need to do it separately IF EXISTS?

+5
source share
3 answers

Yes, with ignore keyword:

INSERT IGNORE INTO `foo` (...) VALUES (...);

IGNORE, , INSERT . , IGNORE, , UNIQUE PRIMARY KEY , . IGNORE , .

, , , ENABLE , :

SET FOREIGN_KEY_CHECKS=0;

-- do your inserts not caring about foreign keys

SET FOREIGN_KEY_CHECKS=1;

, .

, , , - mysql:

MySQL , SQL-, , , InnoDB UNIQUE FOREIGN KEY . InnoDB , . InnoDB ; . SQL, . SQL. InnoDB , , , , , .

, MySQL innoDB .

+6

SQL , - :

insert into Foo (a, b, c)
select
    x.a,
    x.b,
    x.c
from 
    (select
        101 as a,
        202 as b,
        'ABC123' as c
    from dual ) x
    inner join other_table y
        on x.b = y.pkid

, , , .

0

:

INSERT INTO table (col1, col2) SELECT val1, val2 FROM table2 WHERE val3 = 123

WHERE false, INSERT . SELECT val1, val2 .

, , , WHERE.

. MySQL INSERT IF ( if).

0

More articles:


All Articles