Data consistency in DAOs

Looking up "dao data consistency" or "data access layer data consistency" on Google returns nothing useful

Suppose I have to write DAOs that use a SQL db as datasource and let's say that my schema enforces most (but not all) of the business data (?) rules like not null username etc.. but not those that, for the sake of this example, are virtually impossible to check with DDL checks or other SQL mechanisms.

Does this mean that my DAOs have to ensure data consistency, or do they have to be "stupid" and expect the caller to do the appropriate checks?

Practical example with SQL: this schema has 2 tables

create table bank_accounts
(
    accid int primary key,
    balance int not null default 0,
    check(balance >= 0)
);

create table transfer
(
    tid int primary key,
    senderaccid int not null, 
    receiveraccid int not null,
    sendercurrentbalance int not null,
    receivercurrentbalance int not null,
    amount int not null, 
    check(amount > 0),
    foreign key (senderaccid) references bank_accounts(accid),
    foreign key (receiveraccid) references bank_accounts(accid)
    
);

and a typical transaction in SQL would update 2 account balances based on the amount and create a record in transfer for mere presentation only (e.g. list all money transfers in a web app); should I have DAOs that e.g. let me insert a record in transfer without having to do the actual account balance update or should I only have DAOs that act like transactions (by using connection.setAutoCommit(false) of java.sql API etc.) and perhaps even do what the db can't to ensure data consistency as specified by the application requirements?

p.s. if it seems like I'm trying to use a mallet as a wrench it's because it's part of a university project that requires us students to do it the good ol' way without using any fancy new framework or API

Solution

Out of my personal purely practical experience (that probably will sound similar to what other developers have experienced): I always keep DAO methods simple and stupid: their only purpose in life is to bring data from the database (or to store/delete/update) some data in the database.

I don't like the logic in the DAO (if conditions, for loops, delegation to other classes, etc). To answer why do I actually prefer this approach, let me first explain what do I actually do, and then compare:

So, I usually split the code to 3 layers at least:

End points (to handle incoming requests via web, messaging system, etc) - no special logic here, maybe simple validations. This layer is relatively simple.
Backend Services - this is where all the logic of backend lives, including various non-trivial checks, algorithms, etc.
DAOs - similar to the first layer of "end points" but this time at the level of DB interconnection.

Of course the layer of services is the most difficult to implement, however it doesn't have any "external" dependencies like databases, remote http services, etc. we input some data to this layer, it processes the data and sometimes calls DB via DAOs in well-defined and encapsulated points.

This in turn allows relatively simple testing of the service layer (usually with unit tests)

Now as for DAO, its way more complicated job to test those (how will you check that the query is correct without an actual DB under the hood)? So if I'll introduce the logic at the layer DAO I'll add 2 main problems (out of my head, probably there are more of them):

Some pieces of logic will be at the level of DAO whereas other pieces of logic (like complicated checks) will be at the service layer. So it makes the code more messy, harder to understand/navigate/maintain/you name it. Also think what if one shiny day I would like to substitute my Database with something else. If I keep the logic in the DAO, I'll have to re-implement this logic somehow, whereas if I keep my DAOs simple - I just rewrite the queries, which is way easier and faster to do.
How Do I test those pieces of business logic that have "found their way" to the DAO layer? In term of performance DAOs usually require integration testing which is more slow and expensive than the "regular" unit testing . So I'll have to maintain significantly more integration tests for DAOs now - which will lead me to slower overall application build process (tests are something that runs during the build)