You may also have a look at the following articles to learn more —. Submit Next Question. By signing up, you agree to our Terms of Use and Privacy Policy.
Forgot Password? This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. Not always null convention is used as unknown state as in OP question. Imagine weather table with columns country, temp, location. Your requirement might be to gather temperature from country or more specific location in that country, but you don't want more than one entry for country alone or country , location pair.
With MySQL you can't do that easily with just one table which is disappointing. Show 1 more comment. Mark Byers Mark Byers k gold badges silver badges bronze badges.
I don't necessarily disagree that MySQL handles this correctly. The end result is not what I want, though: I end up with duplicates, which is not acceptable. And to me, a "magic" value is just a "fake NULL. It's the "Jim Johnson". There isn't a case if one way or the other way is right. Depending on circumstances you need one or the other. It should be configurable. A similar design mistake was made in Java where all reference types can be null when they needed two schemes.
People keep playing these tugowars and it gets ridiculous. You end up with batteries with only one pole coming out of the factory and some very red faces at the end of the production line. Add a comment. Alexander Yancharuk Alexander Yancharuk I like this solution. But instead of using a trigger, might it make more sense to store the checksum as a generated column?
A generated column worked well for me in a situation where I needed this type of checksum. Heck when we work with physicians office data we have often have two doctors with the same name, address and phone number father and son combinations Your best bet is to have an employee ID if you are inserting employee data to identify each employee uniquely. Mike Lue Mike Lue 4 4 silver badges 8 8 bronze badges. Interesting idea.
I don't feel awesome about it because it involves storing the same exact data twice, but I agree that it would work. I had to deal with this kind of design in the past, and it is terrible, either way with a string or with an int flag. Sometimes people update one thing, sometimes the both - in one order, sometimes the other.
Sometimes something fails outside of a transaction Then you write scripts to validate consistency Add a db trigger to keep the fields in sync. Paul Paul 17 1 1 bronze badge. The null destroys this property as database treats null as unknown Inorder to avoid duplicates and allow null: Make unique key as Primary key. Lordferrous Lordferrous 8 8 silver badges 8 8 bronze badges.
Show 1 more comment. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Helping communities build their own LTE networks. Podcast Making Agile work for data science. Featured on Meta. Warning: Numeric and types other than string may default to 0 or another default value. A simple answer would be : No, it doesn't Explanation : According to the definition of unique constraints SQL A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns This statement can have two interpretations as : No two rows can have same values i.
Which MySQL data type to use for storing boolean values.
0コメント