January 20th, 2014, 01:46 AM
Some basics about foreign key constraint
Hello, I m new to MySQL. I want to know some basic things
1.Is foreign key constraint only meant for SQL DELETE and UPDATE function. Because I can fetch data from two tables with JOIN and simple SELECT, without foreign key constraint in place.
2.Does MySQL foreign key constraints defaults to On Update=RESTRICT, setting "No Action" does the same.
January 20th, 2014, 04:12 AM
Foreign key constraints are used to check the integrity when tables are modified. I.e. when doing insert, update or delete. The constraint itself does not have any significance when doing a select but many DBMS will create an index for maintaining foreign key and this index may be used when doing a select.
Mysql treats (incorrectly with regard to the SQL standard) NO ACTION and RESTRICT as equivivalent. If no update or delete rule is specified Mysql will show RESTRICT (again incorrect as NO ACTION should be default).