![]() Im finding that SHOW CREATE TABLE is not showing foreign key constraints as I. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row. For example: mysql> CREATE TABLE test (a INT NOT NULL AUTOINCREMENT. This is only possible if the respective columns in the referencing table are nullable. ![]() Try deleting from the child row before deleting from the parent row. The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. CREATE TABLE networkclasses ( id TINYINT (1) UNSIGNED NOT NULL AUTOINCREMENT, category VARCHAR (80) NOT NULL, PRIMARY KEY (id), UNIQUE KEY categoryUNIQUE (category), KEY key1 (id. Modify your networkclasses table so that the category field is unique, like below. Most likely, you are trying to delete from a parent row without deleting from a child row. You can only have a foreign key referencing a unique field. It's best to figure out what is causing the error. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. That's why you need the foreign key check. CASCADE: CASCADE will propagate the change when the parent changes. Set NULL: Sets the column value to NULL when you delete the parent table row. Imagine if it happens to a large amount of data. The table containing the foreign key is called the referencing or child table, and the table containing the candidate key is called the referenced or parent table. If there are no foreign key checks, a student could end up having a student id and no user id, which means a student can get an account without being a user, which is wrong. Since every student must have a user id, it would make sense to make the student id from the student accounts table a foreign key that references the primary key user id in the user ids table. ![]() You have one table for user ids, with user id as a primary key and another table for student accounts, with student id as a column. If there are no foreign key constraints, a child row can have a value that is not in the parent row, which would lead to inaccurate data.įor instance, let's say you have a website for students to login and every student must register for an account as a user. You make a foreign key for a reason: because all the values in the child column shall be the same as a value in the parent column. This could lead to inaccurate, misleading, or incomplete data. ![]() It's not a good idea to set a foreign key constraint to 0, because if you do, your database would not ensure it is not violating referential integrity. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |