Re: all foreign key should have index?

> Is it necessary that all foreign key should have index?

It is not necessary from a theoretical standpoint.

It is necessary, from a practical standpoint, if the table pointed to is fairly large, and you want to be able to validate the foreign key references in a reasonable period of time.

> SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE = 'R'
> AND INDEX_NAME IS NOT NULL
>
> Above query does not return any row in my database. Is it bad?

I don't know. Did you create a table in your database called "user_constraints?" That's not one of the standard view names in the SQL-92 INFORMATION_SCHEMA schema, so it's certainly not something that could be considered "standard."

-- 
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/
"The day Microsoft  makes something that doesn't suck  is probably the
day they start making vacuum cleaners" - Ernst Jan Plugge