one-to-one mandatory attendance relationship

Question

As shown in the diagram, there are 2 tables with a one-to-one relationship and mandatory participation. Can you help me implement this as ddl code? I could do it easily when there was no mandatory participation. However, I don't know how I can do it when participation is mandatory.

Answer 1

You cannot create two records in different tables simultaneously and have them reference each other; one record always has to be created first (without a reference to the other table, as that other record won't yet exist) and then the second record can be created with a reference to the first.

If you want the first table to then reference the second table you would have to write a process to do this. Depending on your DBMS this might be a trigger, a stored proc being run on a schedule or some other method.

What you can't do is have a foreign key on each table, referencing the other table, and for both of the FK columns to be NOT NULL

Answer 2

If the CardNo is mandatory per PersonId, then (via Codd's 3NF) it is an ordinary attribute of the PK PersonId.

Third Normal Form
Purpose: compliance with Codd's Relational Model, in order to obtain its full benefit.
Codd: A row is in third normal form iff every non-prime attribute is Functionally Dependent on the Key, the whole Key, and nothing but the Key
Codd's 3NF requires full Functional Dependence on the Key, not "transitive"; etc, that the detractors promote, which is a Relational Primary Key.

Per your declarations, the CardNo is functionally dependent of the Primary Key Person Id, it is 1::1, it is "mandatory".

You don't need a second table, you need a second index on the single table.

CREATE TABLE Person (
    PersonId INT       NOT NULL,
    Name     CHAR(30)  NOT NULL,
    Surname  CHAR(30)  NOT NULL,
    CardNo   INT       NOT NULL,
    PRIMARY KEY PersonId
    UNIQUE CardNo
    )