---

Specifying Constraints on Attributes

---

• Constraints on Attribute Values.

  • In addition to the key constraints:

    primary key key (unqiue) and foreign key

    SQL also allow the user to define a number of constraints on attribute values (domain constraints):

    NOT NULL default value and domain range

  ---

  ---

  ---

  ---

• NOT NULL
  • NOT NULL:  

    specifies that the attribute value cannot take on the NULL value

    The NOT NULL constraint is commonly used along with the primary key and the unique constraints to prevent the use of NULL values in key attributes

    ---

    Example:

    CREATE TABLE test6 ( ssn CHAR(9) NOT NULL, fname CHAR(30), lname CHAR(30) );

    • This insert will succeed:
      insert into test6 values ('123456789', 'John', 'Smith');

      But this insert will fail:

      insert into test6 values (null, 'John', 'Smith');

  ---

  ---

  ---

  ---

• Specifying a default value for an attribute
  • DEFAULT:  

    specifies a default value for an attribute

    ---

    Example:

    CREATE TABLE test7 ( ssn CHAR(9) NOT NULL, salary DECIMAL(6,2) DEFAULT 5000 // Minimum wage );

    • This is a insert using a partial set of attributes:
      insert into test7(ssn) values ('111223333');

      The tuple does not contain the salary attribute

    • The default value 5000 is than assigned to the salary attribute , so effectively:
      insert into test7 values ('111223333', 5000);

  ---

  ---

  ---

  ---

• Specifying a range of values for an attribute
  • CHECK:  

    specifies a range of values for an attribute

    ---

    Example:

    CREATE TABLE test8 ( ssn CHAR(9) NOT NULL, dno INTEGER CHECK (dno > 0 and dno < 21) );

    • This insert will succeed:
      insert into test8 values ('111223333', 11);

    • This insert will FAIL:
      insert into test8 values ('333333333', 44);

    ---

  • Note:

    The second insert will succeed in MySQL (version 5.5.24) !!! MySQL (version 5.5.24) apparently can process the constraint syntax .... but does not implement the constraint testing