FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


what does constraint foreign key means when we create table





badai
I just learned about this and want to share it. If I am wrong you are welcome to correct it.

let say we created these 2 tables:

CREATE TABLE COMPANY (company_id INT NOT NULL,
company_name VARCHAR(50),
PRIMARY KEY (company_id)
) ENGINE=INNODB


CREATE TABLE USER (user_id INT, user_name VARCHAR(50),
company_id INT,
INDEX company_id_idx (company_id),
FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON .....
) ENGINE=INNODB;

the on part could be ON UPDATE and ON DELETE. each can have RESTRICT, NO ACTION, CASCADE and SET NULL.

ON UPDATE RESTRICT: if you try to update a company_id in table COMPANY the engine will reject the operation if at least one USER link to the company that you try to update

ON UPDATE NO ACTION: same as above

ON UPDATE CASCADE : if you update a company_id in table COMPANY the engine will update it accordingly on all USER rows referencing the COMPANY. on update trigger in table USER is not activated

ON UPDATE SET NULL : if you update a company_id in table COMPANY mysql will set related USER company_id to NULL

ON DELETE RESTRICT : if you try to delete a company_id in table COMPANY mysql will reject the operation if at least one USER link on the company

ON DELETE NO ACTION : same as above

ON DELETE CASCADE : if you delete a company row in table COMPANY the engine will delete all related USER. on delete trigger in table USER is not activated

ON DELETE SET NULL : if you delete a row in table COMPANY all related USER mysql will set related USER company_id to NULL

that's it guys.
jmraker
The mysql documentation and examples of foreign keys clauses.
http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html
http://dev.mysql.com/doc/refman/5.7/en/ansi-diff-foreign-keys.html
Related topics
sql schema primary/foreign key question
Can't create mySQL table
foreign key!
mySQL create table problem (parse error)..
How to specify foreign key
mysql insert into multiple tables at once?
PhpMyAdmin and UTF-8
Call a value through Foreign id from another table(DW+PHP)
Can't create new table.
Updating multiple MySQL rows at once
Add Foreign Constraint doubt
quick question on an sql query
MySQL table relations
What does 'Enterprises Pvt. Ltd.' means?
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.