Postgres and Indexes on Foreign Keys and Primary Keys
Does Postgres automatically put indexes on Foreign Keys and Primary Keys? How can I tell? Is there a command that will return all indexes on a table? 8 Answers 8
foreign-keys
How to select rows with no matching entry in another table?
I’m doing some maintenance work on a database application and I’ve discovered that, joy of joys, even though values from one table are being used in the style of foreign keys, there’s no foreign key constraints on the tables. I’m trying to add FK constraints on these columns, but I’m finding that, because there’s already … Read more
When to use “ON UPDATE CASCADE”
I use ON DELETE CASCADE regularly but I never use ON UPDATE CASCADE as I am not so sure in what situation it will be useful. For the sake of discussion let see some code. CREATE TABLE parent ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ); CREATE TABLE child ( id INT NOT … Read more
MySQL DROP all tables, ignoring foreign keys
Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there? 26 s 26 I found the generated set of drop statements useful, and recommend these tweaks: Limit the generated drops to your database like this: SELECT concat(‘DROP TABLE IF EXISTS `’, … Read more
Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)? I know that I can either Use a DELETE without a where clause and then RESEED the identity (or) Remove the FK, truncate the table, and … Read more
What is related_name used for?
What is the related_name argument useful for on ManyToManyField and ForeignKey fields? For example, given the following code, what is the effect of related_name=”maps”? class Map(db.Model): members = models.ManyToManyField(User, related_name=”maps”, verbose_name=_(‘members’)) 6 s 6
How do I see all foreign keys to a table or column?
In MySQL, how do I get a list of all foreign key constraints pointing to a particular table? a particular column? This is the same thing as this Oracle question, but for MySQL. 15 s 15 For a Table: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘<database>’ AND REFERENCED_TABLE_NAME = ‘<table>’; For a Column: … Read more
How to truncate a foreign key constrained table?
Why doesn’t a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID)) drop database mytest; create database mytest; use mytest; CREATE TABLE mygroup ( ID INT NOT NULL … Read more
How can foreign key constraints be temporarily disabled using T-SQL?
Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to drop and then re-create the constraints? 17 s 17 If you want to disable all constraints in the database just run this code: — disable all constraints EXEC sp_MSforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all” To switch them … Read more
