Results 1 to 5 of 5

Thread: Integrity constraints in Database

  1. #1
    Contributing Member
    Join Date
    May 2006
    Answers
    85

    Integrity constraints in Database

    I know constraints are rules placed to achieve the purpose and there by optimization of data in database. But what are Integrity constraints? What all commands come under this?


  2. #2
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Integrity constraints in Database

    There are 5 integrity constrains,

    1. Not Null
    2. Unique
    3. Primary Key
    4. Foreign Key
    5. Check

    Not Null

    A column in a table can be specified not null. It's not possible to insert a null in a NOT NULL column.

    In the following create table statement col_notnull is defined as not null column.

    create table Sample_table(
    col_notnull number not null,
    c number
    );

    insert into Sample_table values ( 1, null); -- legel sql statement
    insert into Sample_table values ( 2, 300); -- legel sql statement
    insert into Sample_table values (null, 500); -- illegal statement.


    The third statement is illegal statement since col_notnull column does not accepts null values.

    Unique Key

    The unique constraint doesn't allow duplicate values in a column. If the unique constraint encompasses two or more columns then the combination must me unique.

    The col_unique column is the unique in the following table create statement

    create table Sample_table(
    col_notnull number not null,
    col_unique number unique,
    c number
    );


    insert into Sample_table values ( 2, 200, 300); -- legel sql statement
    insert into Sample_table values (3, 100,500); -- legel sql statement
    insert into Sample_table values (4, 100,600); -- illegel sql statement

    There is no problem in the first two in insert statements. But the thrid statement fails because we try to insert a duplicate value in unique field.

    insert into Sample_table values ( 1, null, 100); -- legel sql statement
    insert into Sample_table values ( 1, null, 100); -- legel sql statement


    Null values are allowed in Unique columns. So the fourth insert statement executes successfully.

    Since null <> null more than one null values are allowed in a unique field so the last insert statement is also a legel one.

    Primary Key
    On a technical level, a primary key combines a unique and a not null constraint. A table can have at most one primary key.


    create table Sample_table(
    col_notnull number not null,
    col_unique number unique,
    col_primarykey number primary key
    c number
    );




    Foreign Key
    A foreign key constraint (also known as referential integrity constraint) on a column ensures that the value in that column is found in the primary key (or unique key) of another table.

    create table sample_child_table(
    col1 varchar2(20),
    col_forign_key number foreign key references sample_table(col_primary_key)
    )


    Check
    A check constraint allows to state a minimum requirement for the value in a column. The following table allows only numbers that are between 0 and 100 in the column col_check_cons,

    create table Sample_table(
    col_notnull number not null,
    col_unique number unique,
    col_primarykey number primary key,
    col_check_cons number check( col_check_cons between 0 and 100),
    c number
    );

    insert into Sample_table values (3, 100,500,78); -- legel sql statement
    insert into Sample_table values (4, 200,600,101); -- illegel sql statement

    The Second insert statement fails since col_check_cons accepts values only between 0 and 100.



    Hope this helps
    --- James


  3. #3
    Contributing Member
    Join Date
    May 2006
    Answers
    85

    Re: Integrity constraints in Database

    The detailed explanation helped me a lot for learning about the constraints. Can you tell me during alter table can we use all these constraints or is there any restrictions in the usage.


  4. #4
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Integrity constraints in Database

    you have happily add constraints in Alter table commands.
    Since NOT NULL is a column-level constraint you should use alter table...modify clause.

    For others you can use either of alter table...modify clause or alter table...add clause SQL commands.

    ---James


  5. #5
    Contributing Member
    Join Date
    May 2006
    Answers
    85

    Re: Integrity constraints in Database

    Jamesravid it was a very detailed explanation and it helped me a lot to know about integrity constraints. Thank you so much.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact