Turn on one light, when another is turned off
A three-way, or single pole double throw (SPDT), switch should be able to do what you want.


Connect the ungrounded (hot) line feeding the switch, to the common terminal on the three-way switch.
Connect the ungrounded (hot) line feeding the main light, to one of the switched terminals on the three-way switch.
Connect the ungrounded (hot) line feeding the nightlight, to the other switched terminal on the three-way switch.
Connect the grounded (neutral) line feeding the switch, to the grounded (neutral) feeding the lights.
Connect all the grounding conductors together, including a ground to the switch and the box (if it's metal).


When the switch is in one position, the main light will be on. When the switch is toggled the other way, the night light will be on.


Crappy diagram of three-way controlling two lights