CODEWITHSUNDEEP
mysqlsqltypes
ptvty
ptvty

Reputation: 5664

MySQL Data Type with Only 1 Value?

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

Upvotes: 0

Views: 85

Answers (2)

koriander
koriander

Reputation: 3258

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.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270191

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.

Upvotes: 1

Related Questions