1

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).

  • If you can give more information about what those two columns represent it will help with answers. – Joe W Nov 4 '18 at 22:18
  • For example VAT identification number and another code similar to the social security number. The user can submit the first, the second or both, but cannot leave both blank – Lorenzo Fioroni Nov 4 '18 at 22:25
  • Could you put that into the question? Also might help to give a sample row or two of what you are thinking – Joe W Nov 4 '18 at 22:31
  • two columns A and B that cannot have repetitions Separately or together? – Akina Nov 5 '18 at 4:39
  • Thanks to all of you, but the answer that I was searching is the one that @RickJames wrote. Sorry if I was not clear I'm writing my question, next time I will do better – Lorenzo Fioroni Nov 5 '18 at 7:00
0

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>.

|improve this answer|||||
  • Thanks, you have been clear enough to make me understand ;) – Lorenzo Fioroni Nov 5 '18 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.