CODEWITHSUNDEEP
mysqlsqlnullnotnull
suryll
suryll

Reputation: 113

null and not null mysql

Hi I have started moving access into mysql and I was wondering if there is a constraint or something I could use to be able to make a column not null and still have empty values in it?

This is not my own database, if it was I would just fill in the empty fields and then change the column to not null.

Upvotes: 1

Views: 257

Answers (1)

onedaywhen
onedaywhen

Reputation: 57023

Yes, there are various approaches for modelling missing information without using nulls.

You can choose a value to represent missing. It's quite hard to genrealize so here are a few examples. For the end_date attribute in an open-ended period (i.e. has started but is in progress and not yet finished), use a far-future date such as 9999-12-31. For a person_middle_name attributte, Joe Celko suggests placing metadata values in double-curly braces e.g. {{NK}} for 'not known', {{NA}} for 'not applicable', etc.

Another somewhat intuitive approach for modelling missing information is by the absence of a row in a table. If an employee is unsalaried then do not add a row for them in the Payroll table, thus making them distinct from a salaried employee who is currently receiving no salary represented by a salary_amount of zero in the Payroll table.

A further approach is by the presence of a row in a table. You could have tables for Salaried, Unsalaried and SalaryUnknown and ensure every employee has one row in exactly one of these tables (perhaps enforced in MySQL using triggers and/or procedures).

Upvotes: 1

Related Questions