| Bug #3544 | Data Format Constraints | ||
|---|---|---|---|
| Submitted: | 22 Apr 2004 13:34 | Modified: | 2 Dec 2005 17:58 |
| Reporter: | Cornelius Sybrandy | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server | Severity: | S4 (Feature request) |
| Version: | ALL | OS: | Any (ALL) |
| Assigned to: | CPU Architecture: | Any | |
[2 Dec 2005 17:58]
Valeriy Kravchuk
Thank you for a feature request. Specific format can be checked and enforced in MySQL 5 using trigger, for example. See http://dev.mysql.com/doc/refman/5.0/en/triggers.html for details. Check constraints (like in Oracle or other RDBMS) is the other (better, more efficient) way to do it. So, I do not think that any specific "format constraints" will be implemented in MySQL.
Description: There are many different formats that a piece of data can be in. Looking at other DBMS vendors, I've seen specific columns for data types such as IP addresses. A more flexible solution may be to include the ability to apply a format constraint to a column so that all of the data has to be of the specified format. For example, a US phone number can be of the format: 123 456-7890 We could define as varchar/char field of a specific length and have it enforce this format. My first thought was to use Perl compatible regular expressions, so the associated expression could be: \d{3}\s\d{3}-\d{4} This way, instead of adding additional data types for different types of data, we can reuse existing columns and allow the developer to be able to enforce what the data should look like. The only problem I see is sorting. Depending on the data, it may not sort as expected. How to repeat: Currently cannot be done. Suggested fix: Unsure.