Scribd Logo
Upload

Welcome to Scribd!

  • 13 views

    Lecture 14. Indexes and Constraints - MSTeams

    Uploaded by

    iamleilitta
    Very important

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Lecture 14. Indexes and Constraints - MSTeams

    Uploaded by

    iamleilitta
    13 views5 pages
    Very important

    Original Title

    Lecture 14. Indexes and constraints_MSTeams

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Very important

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    13 views5 pages

    Lecture 14. Indexes and Constraints - MSTeams

    Uploaded by

    iamleilitta
    Very important

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 5

    Lecture 14.

    Indexes and Constraints

    This lecture focuses on two database features that indirectly affect the code you
    write: indexes and constraints.

    Indexes

    An index is simply a mechanism for finding a specific item within a resource.


    Each technical publication, for example, includes an index at the end that allows you
    to locate a specific word or phrase within the publication. The index lists these words
    and phrases in alphabetical order, allowing the reader to move quickly to a particular
    letter within the index, find the desired entry, and then find the page or pages on
    which the word or phrase may be found.

    In the same way that a person uses an index to find words within a publication,
    a database server uses indexes to locate rows in a table. Indexes are special tables
    that, unlike normal data tables, are kept in a specific order. Instead of containing all
    of the data about an entity, however, an index contains only the column (or columns)
    used to locate rows in the data table, along with information describing where the
    rows are physically located. Therefore, the role of indexes is to facilitate the retrieval
    of a subset of a table’s rows and columns without the need to inspect every row in
    the table.

    Index Creation

    Consider the department table. You might decide to add an index on the
    name column to speed up any queries that specify a full or partial department name,
    as well as any update or delete operations that specify a department name. Here is
    how you can add such an index to a MySQL database:

    ALTER TABLE department


    ADD INDEX dept_name_idx (name);

    This statement creates an index (a B-tree index to be precise) on the


    department.name column; furthermore, the index is given the name dept_name_idx.
    If there is more than one index on a table, the query optimizer must decide which
    index will be the most beneficial for a particular SQL statement.

    MySQL treats indexes as optional components of a table, which is why you


    must use the alter table command to add or remove an index. Other database servers,
    including SQL Server and Oracle Database, treat indexes as independent schema
    objects. For both SQL Server and Oracle, therefore, you would generate an index
    using the create index command:

    CREATE INDEX dept_name_idx


    ON department (name);

    All database servers allow you to look at the available indexes. MySQL users
    can use the show command to see all of the indexes on a specific table:

    SHOW INDEX FROM department;

    If, after creating an index, you decide that the index is not proving useful, you
    can remove it via the following:

    ALTER TABLE department


    DROP INDEX dept_name_idx;

    SQL Server and Oracle Database users must use the drop index command to
    remove an index:

    DROP INDEX dept_name_idx; (Oracle)


    DROP INDEX dept_name_idx ON department (SQL Server)

    Unique Indexes

    When designing a database, it is important to consider which columns are


    allowed to contain duplicate data and which are not. You can enforce a rule against
    duplicate rows by creating a unique index on the specific column:

    ALTER TABLE department


    ADD UNIQUE dept_name_idx (name);

    SQL Server and Oracle Database users need only add the unique keyword
    when creating an index:

    CREATE UNIQUE INDEX dept_name_idx


    ON department (name);

    You should not build unique indexes on your primary key column(s), since
    the server already checks uniqueness for primary key values.
    Multicolumn Indexes

    Along with the single-column indexes demonstrated thus far, you may build
    indexes that span multiple columns. If, for example, you find yourself searching for
    employees by first and last names, you can build an index on both columns together:

    ALTER TABLE employee


    ADD INDEX emp_names_idx (lname, fname);

    This index will be useful for queries that specify the first and last names or
    just the last name, but you cannot use it for queries that specify only the employee’s
    first name. To understand why, consider how you would find a person’s phone
    number; if you know the person’s first and last names, you can use a phone book to
    find the number quickly, since a phone book is organized by last name and then by
    first name. If you know only the person’s first name, you would need to scan every
    entry in the phone book to find all the entries with the specified first name.

    When building multiple-column indexes, therefore, you should think carefully


    about which column to list first, which column to list second, and so on so that the
    index is as useful as possible.

    Constraints

    A constraint is simply a restriction placed on one or more columns of a table.


    There are several different types of constraints, including:

    1) Primary key constraints – identify the column or columns that guarantee


    uniqueness within a table.
    2) Foreign key constraints – restrict one or more columns to contain only values
    found in another table’s primary key columns, and may also restrict the
    allowable values in other tables if update cascade or delete cascade rules are
    established.
    3) Unique constraints – restrict one or more columns to contain unique values
    within a table (primary key constraints are a special type of unique constraints).
    4) Check constraints – restrict the allowable values for a column.

    Without constraints, a database’s consistency is suspect. For example, if the


    server allows you to change a customer’s ID in the customer table without changing
    the same customer ID in the account table, then you will end up with accounts that
    no longer point to valid customer records (known as orphaned rows). With primary
    and foreign key constraints in place, however, the server will either raise an error if
    an attempt is made to modify or delete data that is referenced by other tables, or
    propagate the changes to other tables for you.

    Constraint Creation

    Constraints are generally created at the same time as the associated table via
    the create table statement. To illustrate, here is an example of creating the product
    table:

    CREATE TABLE product


    (product_cd VARCHAR(10) NOT NULL,
    name VARCHAR(50) NOT NULL,
    product_type_cd VARCHAR (10) NOT NULL,
    date_offered DATE,
    date_retired DATE,
    CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)
    REFERENCES product_type (product_type_cd),
    CONSTRAINT pk_product PRIMARY KEY (product_cd)
    );

    The product table includes two constraints: one to specify that the product_cd
    column serves as the primary key for the table, and another to specify that the
    product_type_cd column serves as a foreign key to the product_type table.
    Alternatively, you can create the product table without constraints, and add the
    primary and foreign key constraints later via alter table statements:

    ALTER TABLE product


    ADD CONSTRAINT pk_product PRIMARY KEY (product_cd);

    ALTER TABLE product


    ADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)
    REFERENCES product_type (product_type_cd);

    If you want to remove a primary or foreign key constraint, you can use the
    alter table statement again, except that you specify drop instead of add:

    ALTER TABLE product


    DROP PRIMARY KEY;
    ALTER TABLE product
    DROP FOREIGN KEY fk_product_type_cd;

    While it is unusual to drop a primary key constraint, foreign key constraints are
    sometimes dropped during certain maintenance operations and then reestablished.

    Cascading Constraints

    With foreign key constraints in place, if a user attempts to insert a new row or
    change an existing row such that a foreign key column does not have a matching
    value in the parent table, the server raises an error. However, you can instruct the
    server to propagate the change to all child rows for you, thus preserving the integrity
    of the data. Known as a cascading update, this variation of the foreign key constraint
    can be installed by removing the existing foreign key and adding a new one that
    includes the on update cascade clause:

    ALTER TABLE product


    ADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)
    REFERENCES product_type (product_type_cd)
    ON UPDATE CASCADE;

    Along with cascading updates, you can specify cascading deletes as well. A
    cascading delete removes rows from the child table when a row is deleted from the
    parent table. To specify cascading deletes, use the on delete cascade clause:

    ALTER TABLE product


    ADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)
    REFERENCES product_type (product_type_cd)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

    Cascading constraints are one case in which constraints do directly affect the
    code that you write. You need to know which constraints in your database specify
    cascading updates and/or deletes so that you know the full effect of your update and
    delete statements.

    Literature
    1. Alan Beaulieu. Learning SQL. 2nd Edition. – O’Reilly Media, 2009. – 337 p.

    You might also like