One-to-one or one-to-many are terms that are descriptive, not prescriptive. It's an observation about the intent of the tables rather than necessarily anything intrinsic to them (although some of it can be enforced).
A primary key/ foreign key relationship always allows a one-to-many relationship: one primary key, referenced in many foreign keys. If the foreign key table has a unique constraint or clustered index on the column, this ensures that the database will enforce a one-to-one relationship between the two tables.
But even in a database where no such unique constraint exists, the relationship between the two tables could still be fairly considered one-to-one (perhaps enforced by the application instead of the database, although this is in my book a Bad Thing) if in practice the foreign key table has no more than one record for every record in the primary key table.
In my experience, having the database enforce the constraint is absolutely required. You can think that you're coding your application so perfectly and well that those extra records could never appear in there, and you want to save some CPU cycles or something not checking about constraints on every data insert.
Wrong. There will eventually be a time, no matter how well you've written, that one of your non-constrainted tables will end up with bad data in it. What I've found is that the constraints actually help me at design time more than they do at customer run time. But all in all, they are indispensable. Just wait until one day you're doing some sort of data repair or mass update at the customer's request and then two weeks later find out you have a mess because of a teensy weensy mistake where you forgot to think about the missing constraints. Or the developer after you did. Or your boss did.
Actually, when we say one-to-one relationship, we often mean a one to zero-or-one relationship. A true one-to-one relationship can't be enforced at the database level because you can't insert to two tables in the same operation. The way I handle this situation is by the use of stored procedures, to ensure both records are properly in place. Alternately, for some database designs, you can do the insert on the foreign key side and use a trigger to automatically create the needed primary key value. But that is for special situations.
Subtyping is an example of a one-to-exactly one relationship that can't be enforced at the database level because the "exactly one" side may be in a different table depending on the type of the primary key row.
-----------------
Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.
