I've got a column in a single of my tables. It's optional, so it may be left blank. However, if your value is supplied for your column, it should be unique. Two questions:
- How do you implement this during my database design (I am using MySQL Work bench, incidentally)
- It is possible to potential trouble with my model?
Only use a
UNIQUE index around the column. See:
A Distinctive index produces a constraint so that all values within the index must be distinct. A mistake happens should you consider adding a brand new row having a key value that suits a current row. For those engines, a distinctive index permits multiple NULL values for posts that can contain NULL. Should you specify a prefix value for any column inside a UNIQUE index, the column values should be unique inside the prefix.
It's could be null (and never blank) and different. Automatically value could be null. There's not a problem for me personally.
You may create a distinctive index on the table. In MySQL work bench that's the UQ checkbox when designingOrmodifying the table.
ALTER the table and
MODIFY the area so
NULLs are permitted.
ALTER TABLE my_table MODIFY my_field VARCHAR(100) NULL DEFAULT NULL;
Step Two, give a
UNIQUE index around the area.
ALTER TABLE my_table ADD UNIQUE INDEX U_my_field (my_field);
It's fine to make use of -- my only hesitation with placing a
UNIQUE index on the nullable area it that it's slightly counter-intuitive initially.
1) Slowly move the column to a different table, allow it to be unique and non-nullable. Now you can possess a row within this new table only if you have something for this.
2) Yes. Secrets and dependencies on secrets would be the foundation of data integrity inside a relational database design. If the attribute should be unique then it ought to be implemented like a key. A nullable "key" isn't a key whatsoever and anyway isn't necessary since it can invariably be moved to a different table making non-nullable without lack of any information.