Scribd Logo
Upload

Welcome to Scribd!

  • 13 views

    A380632037 - 23361 - 15 - 2018 - SQL Constraints

    Uploaded by

    cheppanu dengey

    Copyright:

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

    A380632037 - 23361 - 15 - 2018 - SQL Constraints

    Uploaded by

    cheppanu dengey
    13 views19 pages

    Original Title

    A380632037_23361_15_2018_SQL Constraints

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

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

    A380632037 - 23361 - 15 - 2018 - SQL Constraints

    Uploaded by

    cheppanu dengey

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 19

    SQL Constraints

     Constraints can be specified when the


    table is created with the CREATE TABLE
    statement, or after the table is created with
    the ALTER TABLE statement.
     SQL constraints are used to specify rules
    for the data in a table.
     Constraints are used to limit the type of
    data that can go into a table. This ensures
    the accuracy and reliability of the data in
    the table.
     CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
    );
     NOT NULL - Ensures that a column cannot
    have a NULL value
     UNIQUE - Ensures that all values in a
    column are different
     PRIMARY KEY - A combination of a NOT
    NULL and UNIQUE. Uniquely identifies
    each row in a table
     FOREIGN KEY - Uniquely identifies a
    row/record in another table
     CHECK - Ensures that all values in a
    column satisfies a specific condition
    NOT NULL
     By default, a column can hold NULL
    values.
     The NOT NULL constraint enforces a
    column to NOT accept NULL values.
     By default, a column can hold NULL
    values.
     The NOT NULL constraint enforces a
    column to NOT accept NULL values
    Example:-
    CREATE TABLE Employees09(
    Emp_ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Deparment varchar(255) NOT NULL,
    Acct_No varchar(255) NOT NULL
    );
    UNIQUE Constraint

     The UNIQUE constraint ensures that all


    values in a column are different.
     Both the UNIQUE and PRIMARY KEY
    constraints provide a guarantee for
    uniqueness for a column or set of columns.
     A PRIMARY KEY constraint automatically
    has a UNIQUE constraint.
     However, you can have many UNIQUE
    constraints per table, but only one
    PRIMARY KEY constraint per table.
    EXAMPLE
    CREATE TABLE Person20 (
    ID int NOT NULL UNIQUE, LastName
    varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
    );
     Multiple Columns
    CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,CONSTRAINT UC_Person UNIQUE
    (ID,LastName)
    );
     With Alter Command
    ALTER TABLE Persons
    ADD UNIQUE (ID);
     Drop Unique Constraint
    ALTER TABLE Persons
    DROP CONSTRAINT UC_Person;
    PRIMARY KEY Constraint

     The PRIMARY KEY constraint uniquely


    identifies each record in a database table.
     Primary keys must contain UNIQUE
    values, and cannot contain NULL values.
     A table can have only one primary key,
    which may consist of single or multiple
    fields.
     CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
    );
     On Alter Table
    ALTER TABLE Persons
    ADD PRIMARY KEY (ID);
    • Drop Primary Key
    ALTER TABLE Persons
    DROP PRIMARY KEY;
    FOREIGN KEY Constraint

     A FOREIGN KEY is a key used to link two


    tables together.
     A FOREIGN KEY is a field (or collection of
    fields) in one table that refers to the PRIMARY
    KEY in another table.
     The table containing the foreign key is called the
    child table, and the table containing the
    candidate key is called the referenced or parent
    table.
    PersonID LastName FirstName Age
    1 Hansen Ola 30
    2 Svendson Tove 23
    3 Pettersen Kari 20

    OrderID OrderNumber PersonID


    1 77895 3
    2 44678 3
    3 22456 2
    4 24562 1
     CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFEREN
    CES Persons(PersonID)
    );
    CHECK Constraint

     The CHECK constraint is used to limit the


    value range that can be placed in a
    column.
     If you define a CHECK constraint on a
    single column it allows only certain
    values for this column.
     If you define a CHECK constraint on a
    table it can limit the values in certain
    columns based on values in other columns
    in the row.
     CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
    );
     CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AN
    D City='Sandnes')
    );
    With Alter
    ALTER TABLE Persons
    ADD CONSTRAINT CHK_PersonAge CHECK (
    Age>=18 AND City='Sandnes');
    Drop
    ALTER TABLE Persons
    DROP CONSTRAINT CHK_PersonAge;
    DEFAULT Constraint

     The DEFAULT constraint is used to


    provide a default value for a column.
     The default value will be added to all new
    records IF no other value is specified.
    Example: CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
    );
     With Alter
     ALTER TABLE Persons
    MODIFY City DEFAULT 'Sandnes';

    You might also like