Is it bad form to use "LIMIT 1" on queries that should only return one row anyway? If you have a query like this:  

SELECT * FROM table WHERE primary_key_column = 123 LIMIT 1;


What do you make of the LIMIT 1?
It is technically not necessary, as we are selecting on a unique column. But I have always had the habit of putting the LIMIT 1 on the end as it shows explicitly what you expect.

I think the main negative perhaps is it could mask bad logic in the query that makes it return more than one row, and make a straightforward error harder to solve.

And what about UPDATE or DELETE? This sort of 'belt and braces' approach:

UPDATE table SET a = 'ABC' WHERE primary_key_column = 123 LIMIT 1;


We all know the LIMIT 1 is good sense here when typing queries into the terminal to protect against updating rows we did not intend to. But do you still advocate it for hard-coded and tested queries in the application?

Question is general in nature no specific DBMS in mind.
For SELECT, I think one of the main considerations has to do with performance.

When using LIMIT 1 statistics on row correlations could play a part in determining slower/faster query execution. Although this may not be immediately observable with small data sets, it could become an issue as row numbers and index size grows (depending on DB).

Take this example, where a Postgres user encountered different execution times due to the DB's different assumptions about execution costs (based on available statistical data in the pg_statistic catalog and particularly correlation values) and therefore, resulted in different index scanning strategies.

As a rule of thumb I would use the LIMIT clause only where necessary rather than to externalize my expectations.

For UPDATE and DELETE as already mentioned, the behavior is highly DB dependent.