Mysql – Best structure for MySQL table

database-designMySQLprimary-key

I'm designing my table, but I have doubts: I will have two columns A and B that cannot have repetitions and, in the same row, can't both be null.

I don't know if the best thing to do is to use a primary key with a combination of the values of A and B or a completely different column (for example an autoincremented column).

Best Answer

A PRIMARY KEY does not allow NULLs. But a UNIQUE KEY does. I suggest you try a simple table that has A and B, each nullable, then INSERT suitable combinations to see if they work.

UNIQUE(A,B) says that the pair is unique, while allowing either column to be repeated. Your Question is vague--is that what you wanted?

This will not check for "can't both be null"; that would have to be tested for at the application level.

Based on your Comment, you seem to be asking about the WHERE clause of a SELECT, not the table definition. And it seems like neither column should allow NULLs:

CREATE TABLE ... (
    vat ... NOT NULL,
    ssn ... NOT NULL,
    ...
    PRIMARY KEY(vat),
    UNIQUE(ssn)
)

(or swap the two keys)

Then the application should decide which of these to use:

SELECT ... WHERE vat = ?
SELECT ... WHERE ssn = ?
SELECT ... WHERE vat = ? AND ssn = ?

depending on what is given in the <form>.

Related Question