Cacher is the code snippet organizer for pro developers

# There are two forein key containt metrics you may want:
# - What tables are using columns from <table_name> as foreign keys
# - What columns is <table_name> using that are foreign keys
# Below is how to find either...



# ------------------------------------------------------------------------------
# This query will show you which tables are using columns from <table_name> as
# foreign keys.

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database_name>' AND
  REFERENCED_TABLE_NAME = '<table_name>'



# ------------------------------------------------------------------------------
# This query will show you the table setup of <table_name>, which will show you 
# the foreign key constraints used by <table_name>

SHOW CREATE TABLE `<table_name>`;


  

# If you have a table that you can't drop because of foreign key contraints
# you can force it to be dropped by temporarily turning the foreign key
# checks off before dropping the table:

SET FOREIGN_KEY_CHECKS=0; 
DROP TABLE <table_name>;
SET FOREIGN_KEY_CHECKS=1;

# However, remember that this will leave you with one or more tables that are
# now referencing a table that no longer exists. You should drop those
# contraints. If you drop those contraints first then you should be able to
# drop this table without the above trick though... So really you shouldn't
# need to ever use the above trick unless you're planning on dropping not just 
# this table but also all tables that have contraints with this table (and then
# and tables that have contraints referencing those tables, etc).

# ------------------------------------------------------------------------------
# Creating a foreign key contraint

ALTER TABLE `<thistable_name>`
	ADD CONSTRAINT `<foreign_key_unique_name>`
	FOREIGN KEY (`<thistable_column_name>`)
	REFERENCES `<othertable_name>` (`<othertables_column_name>`)
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION

# example:

ALTER TABLE `nicl_payments`
	ADD CONSTRAINT `nicl_payments__id_students`
	FOREIGN KEY (`id_students`)
	REFERENCES `nicl_students` (`id_students`)
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION


# ------------------------------------------------------------------------------
# Deleting a foreign key contraint

ALTER TABLE `<table_name>` DROP FOREIGN KEY `<foreign_key_unique_name>`;


# ------------------------------------------------------------------------------
# Modifying a foreign key contraint:

# You can't really modify a foreign key contrain. You need to drop it and then 
# recreate it.