Representing attributes in SQL
- If an attribute is mandatory, add the words not null after the attribute's type in the table create statement.
- 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.
page revision: 2, last edited: 20 Aug 2008 15:47