Menu
Seeing as a foreign key does not automatically create an index in SQL Server, I want to create an explicit index on every FK field in my database. And I have over 100 tables in the schema...
So, does anyone have a ready packaged script that I could use to detect all FKs and create an index on each?
490
MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created. Information about foreign keys on InnoDB tables can also be found in the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables, in the INFORMATION_SCHEMA database.
The foreign key constraint alone does not provide the index on Oracle - one must (and should) be created.
If you are inserting data into a dependent table with foreign keys: Each non-null value you insert into a foreign key column must be equal to some value in the corresponding parent key of the parent table. If any column in the foreign key is null, the entire foreign key is considered null.
PostgreSQL automatically creates indexes on primary keys and unique constraints, but not on the referencing side of foreign key relationships. When Pg creates an implicit index it will emit a NOTICE -level message that you can see in psql and/or the system logs, so you can see when it happens.
OK, I worked this out myself - here it is for everyone else's benefit...
select 'create index IX_'+c.name+'_'+p.name+' on '+c.name+'('+cf.name+');'
from sysforeignkeys fk join sysobjects c on fk.fkeyid=c.id
join sysobjects p on fk.rkeyid=p.id
join syscolumns cf on c.id=cf.id and cf.colid = fk.fkey
left join sysindexkeys k on k.id = cf.id and k.colid = cf.colid
where k.id is null
order by c.name
It doesn't work 100%, such as if you have two FKs on one table to the same primary table, but there are few enough instances of this (in my DB at least) that I could viably correct these by hand.
98
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With