probably an obvious question but Oracle Technet is not being friendly with my search terms on this. I want create a table and have a char column have a specific format. For example, a check constraint that would make sure the inserted SSN string is in the format "###-##-####" including the dashes. How do I specify this check?
Add a comment
|
3 Answers
I never done something like it, but try something like this:
CREATE TABLE suppliers
( supplier_id numeric(4),
supplier_name varchar2(50),
CONSTRAINT check_supplier_id
CHECK (supplier_id REGEXP_LIKE(testcol, '^... .. ....$');)
);
1 Comment
Marty
Probably works, but i'm not going to run regex if that's the only option. Thanks
It is generally not a very good idea to store the data in a specific format. Formatting is just bells and whistles and different situations may need different formats. Add localization to this and it becomes more obvious that this is not be a great idea.
You would want to format after retrieving the data. This can be done by Format Models.
1 Comment
Marty
Well best practices aside, the format for the data will always be the same. Though I suppose I can do the check elsewhere instead of in the insert, I just thought it would be nice to make the table only accept that format since it is going to be a static string.
More complex validations can be done using database triggers. Maybe you that's the search term you missed.
