I would state it like this:
- Each Person may be the holder of one or more Accounts.
- Each Account must be held by one and only one Person.
Given that, let's address a few of the questions and statements posed:
Person do not have necessarily one Account (can be null)
The Account would not be null. Instead, there just wouldn't be a row in Account for that Person as they hold no accounts. Now if you write an outer join of Person to Account then yes, for those persons with no accounts the columns from the account, which is the null supplying side, will be null.
When I delete an Account I don't necessarily delete Person
When I delete a Person I want delete Account
How to do that ? (one to one relationship ? on delete cascade ?)
You are right on with a one to many relationship from Person to Account, not a one to one relationship. You are also correct you want deletes of a person to cascade to ensure that when a person is deleted the associated accounts are deleted with it.
In your drawing you have, under the Account table:
Constraints - person_id is unique
and under the Person table:
Constraints - person_id is DELETE_CASCADE, UPDATE_CASCADE
Both of these are not technically correct. Regarding person_id on the Account table, since the relationship is one person to many accounts, the same person_id will be in the account table for each account that person holds. It would look like this:
The account_id is unique, not the person_id.
Regarding person_id as a constraint under the Person table, actually the foreign key constraint is placed on the Account table, referencing the Person table. That constraint is then declared to cascade on delete and update actions. The constraint on the person_id in the Person table would be a primary key constraint.
Here is a revised drawing of the relationships using Oracle SQL Developer Data Modeler:
The oracle tool places an X over the crows foot on the many side to show that the delete rule is CASCADE. It also shows the keys, which show the foreign key constraint with person_id on the Account table, and the primary key constraint with person_id on the Person table.