Representing attributes in SQL
mandatory
If an attribute is mandatory, add the words not null after the attribute's type in the table create statement.
optional
Do not add not null—-i.e., leave it as is.
default value
If an attribute has a default value of X, add the words default X after the attribute's type in the table create statement.
permitted range
To define a permitted range for all values in a column, use the check constraint. This information is listed after the primary key and foreign keys are defined for the table.
check (X > 6)
ensure that the value of X is greater than 6
check (X between 1 and 5)
ensure the value of X is between 1 and 5
check (X >= 3)
ensure the value of X is greater than or equal to 3
check (X in ('M', 'F'))
ensure that X has either the value'M' or 'F'

For example, an employee table might be defined as follows:

create table employee (
  id integer,
  name varchar(25) not null unique,
  sex varchar(6) not null,
  state_addr char(2) default 'MI',
  primary key (id),
  check (sex in ('MALE', 'FEMALE')));

Back to ER to SQL page.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License