Scribd Logo
Upload

Welcome to Scribd!

  • 13 views

    Maintaining Data Integrity in MYSQL

    Uploaded by

    Balakrishna Allu
    The document discusses different constraints in MySQL that can be used to maintain data integrity. It explains the NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and DEFAULT constraints. The NOT NULL constraint enforces that a column cannot be null, UNIQUE ensures each row value is different, PRIMARY KEY combines NOT NULL and UNIQUE (and only one per table), FOREIGN KEY links data between tables, and DEFAULT inserts a preset value if none is provided. Examples are given for creating each constraint type when defining or altering tables.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    Maintaining Data Integrity in MYSQL

    Uploaded by

    Balakrishna Allu
    13 views6 pages
    The document discusses different constraints in MySQL that can be used to maintain data integrity. It explains the NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and DEFAULT constraints. The NOT NULL constraint enforces that a column cannot be null, UNIQUE ensures each row value is different, PRIMARY KEY combines NOT NULL and UNIQUE (and only one per table), FOREIGN KEY links data between tables, and DEFAULT inserts a preset value if none is provided. Examples are given for creating each constraint type when defining or altering tables.

    Original Description:

    MYSQL

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses different constraints in MySQL that can be used to maintain data integrity. It explains the NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and DEFAULT constraints. The NOT NULL constraint enforces that a column cannot be null, UNIQUE ensures each row value is different, PRIMARY KEY combines NOT NULL and UNIQUE (and only one per table), FOREIGN KEY links data between tables, and DEFAULT inserts a preset value if none is provided. Examples are given for creating each constraint type when defining or altering tables.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    13 views6 pages

    Maintaining Data Integrity in MYSQL

    Uploaded by

    Balakrishna Allu
    The document discusses different constraints in MySQL that can be used to maintain data integrity. It explains the NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and DEFAULT constraints. The NOT NULL constraint enforces that a column cannot be null, UNIQUE ensures each row value is different, PRIMARY KEY combines NOT NULL and UNIQUE (and only one per table), FOREIGN KEY links data between tables, and DEFAULT inserts a preset value if none is provided. Examples are given for creating each constraint type when defining or altering tables.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 6

    Maintaining Data Integrity by using Constraints

    NOT NULL Constraint:


    The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. The following example enforces the "P_Id" column and the "LastName" column to not

    accept NULL values:

    mysql>CREATETABLEPersons( P_IdintNOTNULL, LastNamevarchar(30)NOTNULL, FirstNamevarchar(30), Addressvarchar(60), Cityvarchar(20));

    UNIQUE Constraint:

    The UNIQUE constraint uniquely identifies each record in a database table. Column on which UNIQUE constraint is defined can be null or empty.

    The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. We can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. The following example enforces the "P_Id" column must contain unique value:

    mysql>CREATETABLEPersons( P_Idint,

    LastNamevarchar(30), FirstNamevarchar(30), Addressvarchar(70), Cityvarchar(20), UNIQUE(P_Id));

    Defining UNIQUE Constraint on ALTER TABLE:


    To create a UNIQUE constraint on the "P_Id" column when the table is already created, use the following command:

    mysql>ALTERTABLEPersons ADDUNIQUE(P_Id);
    To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

    mysql>ALTERTABLEPersons ADDCONSTRAINTuc_PersonIDUNIQUE(P_Id,LastName);
    To drop a UNIQUE constraint, use the following command:

    mysql>ALTERTABLEPersons DROPINDEXuc_PersonID;

    PRIMARY KEY Constraint:


    Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key. The following example creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:

    mysql>CREATETABLEPersons( P_Idint, LastNamevarchar(30), FirstNamevarchar(30), Addressvarchar(70), Cityvarchar(25), PRIMARYKEY(P_Id));


    To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

    mysql>CREATETABLEPersons( P_Idint, LastNamevarchar(25), FirstNamevarchar(25), Addressvarchar(55), Cityvarchar(25), CONSTRAINTpk_PersonIDPRIMARYKEY(P_Id,LastName));


    To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following syntax:

    mysql>ALTERTABLEPersons ADDPRIMARYKEY(P_Id);
    To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

    mysql>ALTERTABLEPersons ADDCONSTRAINTpk_PersonIDPRIMARYKEY(P_Id,LastName);
    To drop a PRIMARY KEY constraint, use the following syntax:

    mysql>ALTERTABLEPersons DROPPRIMARYKEY;

    FOREIGN KEY Constraint:


    A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Let's illustrate the foreign key with an example. Look at the following two tables:

    The "Persons" table:


    P_Id 1 2 3 LastName Hansen Svendson Pettersen FirstName Ola Tove Kari Address Timoteivn 10 Borgvn 23 Storgt 20 City Sandnes Sandnes Stavanger

    The "Orders" table:


    O_Id 1 2 3 4 OrderNo 77895 44678 22456 24562 P_Id 3 3 2 1

    Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table. The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table. The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. The FOREIGN KEY constraint also prevents that invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to. The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created:

    mysql>CREATETABLEOrders( O_Idint, OrderNoint,

    P_Idint, PRIMARYKEY(O_Id), FOREIGNKEY(P_Id)REFERENCESPersons(P_Id));


    To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following syntax:

    mysql>CREATETABLEOrders( O_Idint, OrderNoint, P_Idint, PRIMARYKEY(O_Id), CONSTRAINTfk_PerOrdersFOREIGNKEY(P_Id)REFERENCES Persons(P_Id));


    To allow naming of a FOREIGN KEY constraint, use the following syntax:

    mysql>ALTERTABLEOrders ADDCONSTRAINTfk_PerOrders FOREIGNKEY(P_Id)REFERENCESPersons(P_Id);


    To drop a FOREIGN KEY constraint, use the following syntax:

    mysql>ALTERTABLEOrders DROPFOREIGNKEYfk_PerOrders;

    DEFAULT Constraint:
    The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. The following example creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

    mysql>CREATETABLEPersons( P_Idint, LastNamevarchar(25), FirstNamevarchar(25), Addressvarchar(55), Cityvarchar(55)DEFAULT'Sandnes');


    To create a DEFAULT constraint on the "City" column when the table is already created, use the following syntax:

    mysql>ALTERTABLEPersons ALTERCitySETDEFAULT'SANDNES';
    To drop a DEFAULT constraint, use the following syntax:

    mysql>ALTERTABLEPersons ALTERCityDROPDEFAULT;

    You might also like