To create a Foreign Key :
1) Convert all MySQL tables from MyISAM to InnoDB.
2) Set Index onto column which u want it to be foreign key.
3) Click Relation view > Choose parent column (primary key for other table) > Choose CASCADE on "ON DELETE" drop down list.
If you can't apply Foreign Key to a particular field, because of error #1216 or #1452 - Cannot add or update a child row: a foreign key constraint fails. Why? Because parent's table doesn't has the records that child's table has, so can't apply foreign key on the child's table. Fortunately, I've found some handy SQL queries using LEFT OUTER JOIN to clean child tables and easily delete unmatched records. As always, don't forget to BACK EVERYTHING UP before attempting these queries!! First, to find "wayward" records with no matching id in a corresponding parent table:
SELECT * FROM `agenda` LEFT OUTER JOIN meetings on
agenda.meeting_id=meetings.meeting_id WHERE
meetings.meeting_id is NULL;
DELETE agenda.* FROM `agenda` LEFT
OUTER JOIN `meetings` ON agenda.meeting_id =
meetings.meeting_id WHERE meetings.meeting_id IS NULL;
Reference at : http://www.mytechmusings.com/2008/04/using-foreign-keys-in-mysql.html and http://lists.mysql.com/mysql/212302
To add a Composite Primary Key :
You can use SQL ALTER TABLE myTable
ADD CONSTRAINT pk_myConstraint PRIMARY KEY (Column1,Column2)
Or use the interface, after the table is created, you can add an index (type 2 or 3 in textbox of columns). Then select columns to be the composite primary key, then choose "Primary Key" from Index Type drop down list.
Reference at : http://www.sitepoint.com/forums/mysql-182/composite-key-through-phpmyadmin-290795.html and http://stackoverflow.com/questions/1545571/how-do-i-make-a-composite-key-with-sql-server-management-studio
To reset auto increment id on table :
You don't need to waste time to create a new table, you can just delete all records in existing table then run SQL : ALTER TABLE `table_name` AUTO_INCREMENT =0
Reference at : http://www.knowledgesutra.com/discuss/ttlist-reset-auto-increment-phpmyadmin.om
To find where is the foreign keys of particular primary key (getting error when trying to rename the field of primary key because there is foreign key on other table linked by the primary key) :
There are 2 different solutions :
1) SQL to list out all tables that has foreign keys of a particular primary key :
select ku.*
from INFORMATION_SCHEMA.table_constraints tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku ON
(tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME and tc.CONSTRAINT_SCHEMA = ku.CONSTRAINT_SCHEMA )
where constraint_type='FOREIGN KEY' and ku.REFERENCED_TABLE_NAME = 'your_table_name'
2) You go rename the field to get error, then type SQL :
SHOW ENGINE INNODB STATUS
Then look at the "LATEST FOREIGN KEY ERROR" section. Reference at http://stackoverflow.com/questions/7463221/sql-lists-out-all-foreign-keys-of-a-primary-key
SERIAL is the best datatype for auto increament id field, because SERIAL is BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. It can accept integers from 0 up to 18446744073709551615 (max is 20 digits only), if I assign length 50, it will still accept 20 digits only, it won't accept 50 digits. 18446744073709551615 is the largest integer accepted. You cannot use 2 Serial on two fields in a table because 1 table can has 1 AUTO_INCREMENT field only. The word UNSIGNED is attribute. Reference at here.
I cannot find the different between BIGINT Signed and Unsigned, however the different between CHAR Signed and Unsigned :
-Signed char, which gives you at least the -128 to 127 range.
-Unsigned char, which gives you at least the 0 to 255 range.
-Signed number uses one bit to determine whether the number is positive or negative, an unsigned number is positive.
Reference at here.
Different between datatype Char and Varchar :
Char :
- Use Char if all of records values have the same length. Eg, if u set Char to 5 characters, each records will be 5 bytes.
- Don't use Char if all of records values have difference length. Eg, u store "abc123" on Char(10), it will be "0000abc123" or "abc1230000", u need extra work to delete all 0000. This feature could lead to many irritating bugs during development and makes testing harder). Info at here.
- Char can be faster and more space efficient.
- Use Char if you need to update the records frequently, because it won't grow the size everytime update the records.
Varchar :
- Use Varchar if all of records values have different length. Eg, if you save record "love1234", the size will be 8 bytes (to save the value) + 1 byte (to save the length of value). Varchar need extra 1 or 2 bytes to save the length of value. Info of datatype at here. The example of size of data / records are at here.
- Don't use Varchar if records need to be updated frequently because their size will grow, which can cause extra work. if a row grows and no longer fits in its original location, the behavior is storage engine-dependent. Info at here.
Example :
store the word "FooBar" :
CHAR(6) = 6 bytes (no overhead)
CHAR(10) = 10 bytes (4 bytes of overhead)
VARCHAR(10) = 7 bytes (1 byte of overhead)
Conclusion : Use Char only if you have fixed length of records values. Don't use Char if the length of records values are different, even 1 character different also must NOT use Char.
Comparison between Varchar vs Text at here.
Normalization :
If some of the fields are missing (null) for a bunch of the rows, they make a good candidate for splitting off -- the extra table would have fewer rows. You would use LEFT JOIN to put things back together when needed. Eg :
FROM t1 JOIN t2 on t1.ID = t2.ID
JOIN / INNER JOIN show the records on both tables. LEFT JOIN / LEFT OUTER JOIN show the records on 1st table. RIGHT JOIN / RIGHT OUTER JOIN show the records on 2nd table.
0 comments