SQL Reserved Words

Within the OpenMRS Data Model and recommended for all module authors developing their own SQL tables, we use the following conventions:

  • lowercase
  • underscore, when needed, between terms
  • use singular forms of objects (e.g., "patient", not "patients")

We make explicit exceptions to the last rule (using singular form), when a term conflicts with a reserved word in SQL.  For example, we have tables lke "users" and "orders", since their singular form is a SQL reserved word.

Not Reserved (but good to avoid anyway)

action bit date enum no text time timestamp value

Reserved Words in SQL

accessible add after aggregate all alter analyze and as asc asensitive auto_increment avg avg_row_length before between bigint binary blob bool both by call cascade case change char character check checksum collate column columns comment condition constraint continue convert create cross current_date current_time current_timestamp current_user cursor data database databases datetime day day_hour day_microsecond day_minute day_second dayofmonth dayofweek dayofyear dec decimal declare default delay_key_write delayed delete desc describe deterministic distinct distinctrow div double drop dual each else elseif enclosed end escape escaped exists exit explain false fetch fields file first float float4 float8 flush for force foreign from full fulltext function global grant grants group having heap high_priority hosts hour hour_microsecond hour_minute hour_second identified if ignore in index infile inner inout insensitive insert insert_id int int1 int2 int3 int4 int8 integer interval into is isam iterate join key keys kill last_insert_id leading leave left length like limit linear lines load local localtime localtimestamp lock log logs long longblob longtext loop low_priority master_ssl_verify_server_cert match max max_rows mediumblob mediumint mediumtext middleint min_rows minute minute_microsecond minute_second mod modifies modify month monthname myisam natural no_write_to_binlog not null numeric on optimize option optionally or order out outer outfile pack_keys partial password precision primary privileges procedure process processlist purge range read read_only read_write reads real references regexp release reload rename repeat replace require restrict return returns revoke right rlike row rows schema schemas second second_microsecond select sensitive separator set show shutdown smallint soname space spatial specific sql sql_big_result sql_big_selects sql_big_tables sql_calc_found_rows sql_log_off sql_log_update sql_low_priority_updates sql_select_limit sql_small_result sql_warnings sqlexception sqlstate sqlwarning ssl starting status straight_join string table tables temporary terminated then tinyblob tinyint tinytext to trailing trigger true type undo union unique unlock unsigned update usage use user using utc_date utc_time utc_timestamp values varbinary varchar varcharacter variables varying when where while with write xor year year_month zerofill