Read consistency depends on the active Transaction Isolation Level. Here, you trade off consistency requirements against performance and additional error states.
Most applications get away with rather "weak" isolation guarantees, because a JOIN would still not return inconsistent data. For example,
CREATE TABLE one (a INTEGER PRIMARY KEY, b INTEGER NOT NULL);
CREATE TABLE two (c INTEGER PRIMARY KEY, d INTEGER NOT NULL REFERENCES one(a));
You would normally insert values with surrogate keys like
INSERT INTO one VALUES(nextval('a_seq'), '5');
INSERT INTO two VALUES(nextval('c_seq'), currval('a_seq'));
Technically, these are two transactions, so they become independently visible even on the highest isolation level, but if your query asks for
SELECT FROM one JOIN two ON a = d;
you will still only get complete sets. For most applications, that is sufficient, and avoiding the extra application logic required for stronger isolation levels (where transactions can be rejected seemingly randomly due to concurrent transactions) is preferable.
The database will reject transactions that cause a constraint to be violated, but illegal intermediate states in the middle of a transaction are allowed. For example,
DELETE FROM one WHERE a = 1;
DELETE FROM two WHERE d = 1;
would give an error if such a row existed in one; the same transactions in the reverse order are okay, as is wrapping both statements into a single transaction with BEGIN and COMMIT. If you like to live dangerously, you can also give an ON DELETE CASCADE rule that removes all dependent rows.
Consistency is seen from the point of view of the database server: the query is sent to the server, processed there, and the result set returned. The network latency is irrelevant for consistency: two clients that are not otherwise communicating with each other have no way of finding out which of them has sent its query first, so the server chooses an arbitrary order.
If the two clients do talk to each other, and one notifies the other that it has just inserted data and got an acknowledgement from the server, then if the second client will be able to see the data on its query.
Replicated databases will still keep this property, but at much stiffer performance penalties for higher isolation levels, because acknowledging a transaction needs all nodes to confirm that no other active transaction conflicts with it, and getting this consensus will take time.
At higher isolation levels, clients that have started a new transaction with BEGIN can perform SELECT queries on a frozen view of the database that does not reflect any other transactions that are in flight, but any data read will be locked until the end of the transaction, making concurrent writes fail.
From an application programmer point of view, you would typically perform modification inside the DBMS instead of retrieving data, modifying it and writing it back, e.g.
UPDATE accounts SET balance = balance - 1 WHERE user = '3';
This nicely avoids consistency problems with other transactions even at lower isolation levels, and thus lets you get away without locks: two such UPDATE queries can be sent from different clients, and the DBMS can resolve them internally, and without delay from network turnaround times.
Constraints need not be referential: A constraint that balance needs to be positive would be verified for each transaction, if only one can be executed, then the second is returned with an error (for concurrent transactions, which one is "second" is arbitrary).
These constraint checks are one of the main reasons why you'd want to use an RDBMS, but this is limited to data structures that can be expressed as relations between a finite amount of tables and appropriate indexes, so for example it is difficult to express graphs or hierarchies in a way that allows efficient queries, this is where NoSQL comes in.
