Database Tutorial

Course Tutorial Site

Site Admin

Old Constraints

with 11 comments

Database constraints are user-defined structures that let you restrict the behaviors of columns. There are five principal types of constraints covered in this web page.

You can create these constraints when you define a table with a SQL CREATE statement. A constraint defined on the same line as its column is called an inline constraint, while a constraint on its own line in a CREATE statement is an out-of-line constraint. Out-of-line constraints must reference (or point to) the column that they constrain. You can also add, modify, or drop these constraints with an ALTER statement after you’ve created the table.

While all databases support the set of possible constraints. They sometimes provide system specific alternatives. The following chart describes the types of restrictions you can place on columns and tables by using database-level constraints and triggers.

ConstraintsMap

Contraint Column Definition Row Definition Table Definition External Definition Constraint Not Null Column Not Null Row Not Null Table Not Null External Not Null Constraint Check Column Check Row Check Table Check External Check Constraint Unique Column Unique Row Unique Table Unique External Unique Constraint Primary Key Column Primary Key Row Primary Key Table Primary Key External Primary Key Constraint Foreign Key Column Foreign Key Row Foreign Key Table Foreign Key External Foreign Key Constraint Index Column Index Row Index Table Index External Index Constraint Trigger Column Trigger Row Trigger Table Trigger External Trigger

A column-level constraint may restrict the minimum cardinality of a column. When a column is restrictied, any INSERT or UPDATE statements must provide a value for that column to add or change a row respectively.

Variations on the generalized concept of a constraint are found in the NOT NULL and CHECK constraints. A NOT NULL can universally impose a minimum cardinality constraint on a column. That means you must provide a value to the constrained column with any INSERT or UPDATE statement against the table. A CHECK constraint can sometimes (implementation dependent) let you impose a minimum cardinality constraint on a collection of columns.

The data type of a column generally sets the maximum cardinality of a column. For example, a scalar variable can only hold one thing. On the other hand a collection may hold many things.

Cardinality is the combination of the minimum and maximum cardinality, respectively the least number of things in a column or relationship and the most number of things in the same column or relationship. Cardinality is represented by numbers for the minimum and maximum cardinality separated by two dots. For example, ANSI compliant columns are nullable, or have a zero to one cardinality, 0..1 (see the UML page for broader coverage).

As mentioned, scalar data types limit the maximum cardinality to one, which means a scalar column’s cardinality is 0..1 when the column is nullable. A nullable column exists when you can create or modify the column to let it contain a null value. A column value is thereby optional. This is supported by some IDE tools. The cardinality of a not null column is constrained to require a value and is represented by 1..1 cardinality. Not null columns are mandatory because they require you to provide a value.

Collection data types set the maximum cardinality to many (dependent on the implementation, this may be a bounded or an unbounded many). The cardinality for an optional collection data type column is 0..*, while the not null collection data type column is 1..*.

There are five types of database constraints. They are:

You may define constraints when you create tables, or defer their creation and use an ALTER TABLE command. However, the NOT NULL constraint can’t be added with the ALTER TABLE command. Oracle requires that you add a NOT NULL constraint as an inline constraint. Unfortunately, inline NOT NULL constraints often don’t receive meaningful names and often rely on system generated sequence names in Oracle. NOT NULL constraints are an attribute of a table’s column in MySQL and can’t have a name.

Written by michaelmclaughlin

September 27th, 2009 at 12:33 am

Posted in

11 Responses to 'Old Constraints'

Subscribe to comments with RSS or TrackBack to 'Old Constraints'.

  1. For the “Check Constraint” under EXTERNAL, there is a green check mark. Shouldn’t that be an ‘X’? It says, “A CHECK constraint is an internal constraint and can’t reference external values in another table, like a foreign key constraint. ” To me, I understand that as not being able to reference external values, meaning it’s not possible or maybe it is. Can you please clarify?

    Thanks!

    Trevor D

    13 Jan 10 at 10:11 pm

  2. Yes, it’s not possible. I lost the original image. I’m working on changing it. Also, I’m going to increase the columns by creating one for a single column and another for multiple columns. Great catch! 😉

    michaelmclaughlin

    13 Jan 10 at 10:20 pm

  3. MySQL does have a CHECK constraint (if you can call it a constraint), but the book says “the MySQL server allows check constraints to be defined but does not enforce them.” This seems pointless; why set a rule if it doesn’t have to be followed?

    Ben Davidson

    28 Apr 10 at 7:06 am

  4. Good point. If you find an answer, let me know. I’ve no clue. That’s why I say it doesn’t. A feature devoid of implementation isn’t a feature.

    michaelmclaughlin

    21 Jun 10 at 10:26 pm

  5. In the section:
    Oracle Examples of FOREIGN KEY Constraints

    Your code shows an inline foreign key constraint on grandma_id in the format of an out of line constraint and doesn’t run.

    CREATE TABLE tweetie_bird
    ( tweetie_bird_id NUMBER CONSTRAINT pk1_tweetie_bird PRIMARY KEY
    , tweetie_bird_house VARCHAR2(30) CONSTRAINT nn1_tweetie_bird NOT NULL
    , grandma_id NUMBER CONSTRAINT fk1_tweetie_bird FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id));

    If the foreign key constraint on grandma_id is suppose to be out of line then a comma will need to be added.
    If the constraint should be inline then the code from “CONSTRAINT” to “(grandma_id)” needs to be deleted.

    Jared Morris

    28 Aug 10 at 3:50 pm

  6. On the MySQL foreign keys, should it have a comma after each constraint you set?

    ALTER TABLE tweetie_bird
    ADD CONSTRAINT pk1_tweetie_bird PRIMARY KEY (tweetie_bird_id), but this comma is not shown above.

    Stephen Thompson

    20 Sep 10 at 10:50 am

  7. Yes, it’s fixed. Also, a VARCHAR2 was in the previous statement. It’s now a VARCHAR.

    michaelmclaughlin

    3 Oct 10 at 2:45 pm

  8. I am still seeing commas for Oracle and not for MySQL.

    Kyle Redfearn

    14 Jan 11 at 12:04 am

  9. Can you be more specific where you’re finding the error?

    michaelmclaughlin

    16 Mar 11 at 2:23 pm

  10. Shouldn’t the ALTER statement for the foreign key MYSQL example have a comma between the ADD statements?

    Eli

    30 Apr 11 at 8:29 pm

  11. database constraints

    rajashree borah

    28 Jun 12 at 10:47 am

Leave a Reply