What are the consequences of destroying the USS Constitution?

The biggest direct impact to your character is that you will miss out on being rewarded with the Broadsider (A powerful weapon that fires cannonballs. Rewarded to you if you side with Ironsides.)

From a story standpoint, if you side with the scavengers Davies will sabotage the rockets so that the ship will explode. After the rockets explode the ship will remain landlocked and the robot crew will turn hostile. You will then have to storm the boat with the scavengers and defeat Ironsides. Regardless of what dialogue option you choose the scavengers must be defeated to complete the quest.