0

Is there any way I can create a column with only one value?
Just true for example, and no other values!

I want to make a Boolean type with NULL.

( true=TRUE | NULL=FALSE )

Thanks

Positivity
  • 5,406
  • 6
  • 41
  • 61
  • Try using an enum with only one value. – Gordon Linoff Feb 08 '14 at 22:58
  • How? `ALTER TABLE test ADD col ENUM( 1 ) NULL ;` gives an error. Thank you – Positivity Feb 08 '14 at 23:02
  • Out of curiousity, what is the point? Can't you simply have: (true=TRUE|NULL=FALSE|false=FALSE) if that is truly your desire...or simply just have it as true or false without caring about null and just ensure null is never entered into the db? (just wondering because im currently creating a db and don't understand the reasoning behind wanting something like this) – TheOneWhoPrograms Feb 08 '14 at 23:04
  • what's wrong with using boolean or you could use int(1) signed which you can set to 1 or NULL – malta Feb 08 '14 at 23:09
  • @TheOneWhoPrograms Just curiosity! :D , well I thought it may be a better practice (in term of efficiency or something) to have only what I need, not three indicators for two situations. – Positivity Feb 08 '14 at 23:09
  • See http://stackoverflow.com/questions/289727/which-mysql-datatype-to-use-for-storing-boolean-values, which suggests `CHAR(0)` as a possible solution. – Ilmari Karonen Feb 08 '14 at 23:12
  • You'd want to use a tinyint, not an int(1). tinyint is 1 byte, int is 4 bytes. tinyint still has a range of -64 to 64. Also, an enum with one value will not have less storage than 1 byte. Even a bool datatype is 1 byte. – Miro Feb 08 '14 at 23:13
  • 1
    Ah, okay ;) Thanks Webinan. I think your best best would be to go with korianders' answer. That is truly the most efficient case you can hope for (from my limited knowledge :) ) – TheOneWhoPrograms Feb 08 '14 at 23:19
  • @IlmariKaronen `CHAR(0)` Is a nice answer to my question, but I didn't knew things in koriander's answer! So I'll go with that. – Positivity Feb 08 '14 at 23:23

2 Answers2

3

If your concern is efficiency, then the best option is to use BIT(1) (in more recent versions) without allowing for NULLs. Allowing for NULLs in fields actually costs more space and performance because it needs an extra data structure.

koriander
  • 3,110
  • 2
  • 15
  • 23
1

The idea of adding an enum with one value was a guess. The DB designers clearly thought it useless though.

You can take the approach of adding a constraint that the value always be 1 or NULL, on some appropriate field whose type is of your choosing.

Alas, MySQL doesn't implement check constraints. So you have two choices.

The first is to use update/ insert triggers to enforce the logic. Yuck, but possible.

The other way is a foreign key constraint that refers to a table with only one row. That is a relatively easy way to enforce this condition.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786