I am trying to memorize some sql syntax and I have gotten the ALTER TABLE ADD CONSTRAINT syntax down. I believe I am correct when I say that when you use this syntax to add a FOREIGN KEY or PRIMARY KEY constraint, that sql server automatically creates indexes to support the constraint operations. (Is that true...or is it only true on the PK but not the FK?)
If so, when you use the ALTER TABLE DROP CONSTRAINT syntax...are the supporting indexes automatically dropped as well? Can these implicit supporting indexes be explicitly dropped? If so is the CONSTRAINT automatically removed?
I am just wanting to know how it works "under the covers". Googling has not helped. I imagine I could have queried some sys tables to discover the truth but thought I would try here instead.
Thanks for your help.