Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Auditing is always fun when we do code reviews

  • Auditing is always fun when we do code reviews. And in a product backend check we found a lot of junk names for the Default constraints inside the database. What are the different ways you can create a CONSTRAINT inside SQL Server?

    Posted on 01-17-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

6  Answers  

Subscribe to Notifications
  • Score
    10

    The primary job of a constraint is to enforce a rule in the database. Together, the constraints in a database maintain the integrity of the database.Each one plays an important role in your database architecture.

    The following are the constraints in SQL server

    1. Primary Key Constraint - specify fields that uniquely identify each record in the table. (NULL is not allowed.)
    2. Foreign Key Constraint - are fields in a relational database table that match the primary key column of another table to enforce data integrity. Both datatype should be the same. The optimizer uses foreign key constrains and check constraints to create more efficient query plan by skipping some part of the query plan because the optimizer can see that there is a foreign key constraint so it is not necessary to execute that part of the plan.Consider creating an index for every foreign key constraint.
    3. Default Constraint - allow you to specify a value that the database will use to populate fields that are left blank in the input source.
    4. Unique Constraint - specify fields that uniquely identify each record in the table.NULL is allowed for one time.
    5. Check Constraint - allow you to limit the types of data that users may insert in a database. If you have a choice between using a trigger or a CHECK constraint to enforce rules or defaults within your SQL Server databases, you will generally want to choose a CHECK constraint, as they are faster than using triggers when performing the same task.Rules are provided for backward compatibility and have been replaced by CHECK constraints. Constraints are much more efficient than rules and can boost performance. Rules have some restrictions. Avoid using CHECK_CONSTRAINTS hint with bulk copy program.

    6. NOT NULL Constraint - NOT NULL constraints in Microsoft SQL Server allow you to specify that a column may not contain NULL values.(It behaves like a constraint.)

    It is always a good practice to create the constraint with a proper name. Otherwise as discussed in the question there would be a possiblity for junk constraint names there by many confusion. Following are the two ways to create constraints in SQL server with proper naming conventions.

    The Query Optimizer can use constraints to help it select the best performing query. In order to get the best performance, it is important that you use them appropriately throughout your database application, such as using them for primary keys, unique keys, primary keys, and so on

    Tips for using SQL Server 2008 constraints

    1. Use CHECK constraints instead of triggers whenever possible.

    2. Use CHECK constraints instead of rules.

    3. Consider creating unique constraints and primary keys on user-defined table types.

    4. Avoid using CHECK_CONSTRAINTS hint with bulk copy program.

    5. Consider creating column-level constraints instead of the table-level constraints.

    6. Consider creating an index for every foreign key constraint.

    7. Try to create a single column constraint.

    8. Consider creating a surrogate integer primary key.

    9. Use cascading referential integrity constraints instead of triggers whenever possible.

    10. Use default constraints instead of DEFAULT objects.

    11. When you create primary key or unique key constraints, specify the CLUSTERED or NONCLUSTERED keyword.

    12. Create NONCLUSTERED primary key for identity column.

    13. When you create primary key or unique key constraints, specify a FILLFACTOR keyword.

    14. Consider using the NOT FOR REPLICATION clause of the FOREIGN KEY and CHECK constraints.

    **--Method 1:At Declaration level**
    
    CREATE TABLE SQLTypes(
        [Type] CHAR(10) NOT NULL
            CONSTRAINT UNQ__SQLTypes__Type UNIQUE
    )
    
    CREATE TABLE SQLZealot (
          ID int NOT NULL
            CONSTRAINT PK__SQLZealot_ID PRIMARY KEY
        , SalesPrice money NOT NULL
            CONSTRAINT CH__SQLZealot__SalesPrice
                CHECK (SalesPrice >= 1 AND SalesPrice <=100)
        , [Type] CHAR(10) NOT NULL
            CONSTRAINT FK__SQLZealot__Type FOREIGN KEY REFERENCES SQLTypes([Type])
        , DateMade datetime NOT NULL
            CONSTRAINT DF__SQLZealot_DateMade DEFAULT GetUtcDate()
    );
    
    Drop table SQLTypes
    Drop Table SQLZealot
    
    
    **--MEthod 2:At Later level**
    
     CREATE TABLE SQLTypes(
        [Type] CHAR(10) NOT NULL
    )
    
    CREATE TABLE SQLZealot (
          ID int NOT NULL
        , SalesPrice money NOT NULL
        , [Type] CHAR(10) NOT NULL
        , DateMade datetime NOT NULL
    );
    
     Alter table SQLTypes Add CONSTRAINT UNQ__SQLTypes__Type UNIQUE(Type)
    
     Alter table SQLZealot Add CONSTRAINT PK__SQLZealot_ID PRIMARY KEY(ID)
    
     Alter table SQLZealot Add CONSTRAINT CH__SQLZealot__SalesPrice
                CHECK (SalesPrice >= 1 AND SalesPrice <=100)
    
     Alter table SQLZealot Add CONSTRAINT FK__SQLZealot__Type FOREIGN KEY(Type) REFERENCES SQLTypes([Type])
    
     Alter table SQLZealot Add CONSTRAINT DF__SQLZealot_DateMade DEFAULT GetUtcDate() for Datemade
    
    Drop table SQLTypes
    Drop Table SQLZealot
    
    Replied on Jan 17 2012 12:21AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    8

    The primary job of a constraint is to enforce a rule in the database. Together, the constraints in a database maintain the integrity of the database.

    Constraints are used to limit the type of data that can go into a table.

    Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

    Data integrity rules fall into one of three categories: entity, referential, and domain. We want to briefly describe these terms to provide a complete discussion.

    Entity Integrity

    Entity integrity ensures each row in a table is a uniquely identifiable entity. You can apply entity integrity to a table by specifying a PRIMARY KEY constraint. For example, the ProductID column of the Products table is a primary key for the table.

    Referential Integrity

    Referential integrity ensures the relationships between tables remain preserved as data is inserted, deleted, and modified. You can apply referential integrity using a FOREIGN KEY constraint. The ProductID column of the Order Details table has a foreign key constraint applied referencing the Orders table. The constraint prevents an Order Detail record from using a ProductID that does not exist in the database. Also, you cannot remove a row from the Products table if an order detail references the ProductID of the row.

    Entity and referential integrity together form key integrity.

    Domain Integrity

    Domain integrity ensures the data values inside a database follow defined rules for values, range, and format. A database can enforce these rules using a variety of techniques, including CHECK constraints, UNIQUE constraints, and DEFAULT constraints. These are the constraints we will cover in this article, but be aware there are other options available to enforce domain integrity. Even the selection of the data type for a column enforces domain integrity to some extent. For instance, the selection of datetime for a column data type is more restrictive than a free format varchar field.

    The following list gives a sampling of domain integrity constraints.

    • A product name cannot be NULL.
    • A product name must be unique.
    • The date of an order must not be in the future.
    • The product quantity in an order must be greater than zero.

    We will focus on the following constraints:

    NOT NULL

    UNIQUE

    PRIMARY KEY

    FOREIGN KEY

    CHECK

    DEFAULT


    Different ways you can create a CONSTRAINT inside SQL Server


    SQL 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 SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:

    CREATE TABLE Persons
        (
        	P_Id int NOT NULL,
        	LastName varchar(255) NOT NULL,
        	FirstName varchar(255),
        	Address varchar(255),
        	City varchar(255)
        )
        GO
    



    SQL UNIQUE Constraint

    The UNIQUE constraint uniquely identifies each record in a database table.

    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.

    Note:: that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

    SQL UNIQUE Constraint on CREATE TABLE

    The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:

    CREATE TABLE Persons
        (
        	P_Id int NOT NULL UNIQUE,
        	LastName varchar(255) NOT NULL,
        	FirstName varchar(255),
        	Address varchar(255),
        	City varchar(255)
        )
        GO
    

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

    CREATE TABLE Persons
    (
    	P_Id int NOT NULL,
    	LastName varchar(255) NOT NULL,
    	FirstName varchar(255),
    	Address varchar(255),
    	City varchar(255),
    	CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
    )	
    GO
    

    SQL UNIQUE Constraint on ALTER TABLE

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

    ALTER TABLE Persons
    ADD UNIQUE (P_Id)
    GO
    

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

    ALTER TABLE Persons
        ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
        GO
    

    To DROP a UNIQUE Constraint

    To drop a UNIQUE constraint, use the following SQL:

    ALTER TABLE Persons
        DROP CONSTRAINT uc_PersonID
        GO
    

    There is no difference between Unique Index and Unique Constraint. Even though syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index or Primary Key Index are physical structure that maintain uniqueness over some combination of columns across all rows of a table. It is a convenient way to enforce a Unique Constraint for SQL Server.



    SQL PRIMARY KEY Constraint

    The PRIMARY KEY constraint uniquely identifies each record in a database table.

    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.

    SQL PRIMARY KEY Constraint on CREATE TABLE

    The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:

    CREATE TABLE Persons
        (
        	P_Id int NOT NULL PRIMARY KEY,
        	LastName varchar(255) NOT NULL,
        	FirstName varchar(255),
        	Address varchar(255),
        	City varchar(255)
        )
        GO
    

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

    CREATE TABLE Persons
        (
        	P_Id int NOT NULL,
        	LastName varchar(255) NOT NULL,
        	FirstName varchar(255),
        	Address varchar(255),
        	City varchar(255),
        	CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
        )
        GO
    

    SQL PRIMARY KEY Constraint on ALTER TABLE

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

    ALTER TABLE Persons
        ADD PRIMARY KEY (P_Id)
        GO
    

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

    ALTER TABLE Persons
        ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
        GO
    

    Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

    To DROP a PRIMARY KEY Constraint

    To drop a PRIMARY KEY constraint, use the following SQL:

    ALTER TABLE Persons
        DROP CONSTRAINT pk_PersonID
        GO
    



    SQL FOREIGN KEY Constraint

    A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

    The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created:

    CREATE TABLE Orders
        (
        	O_Id int NOT NULL PRIMARY KEY,
        	OrderNo int NOT NULL,
        	P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
        )
        GO
    

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

    CREATE TABLE Orders
        (
        	O_Id int NOT NULL,
        	OrderNo int NOT NULL,
        	P_Id int,
        	PRIMARY KEY (O_Id),
        	CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
        	REFERENCES Persons(P_Id)
        )
        GO
    

    SQL FOREIGN KEY Constraint on ALTER TABLE

    To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:

    ALTER TABLE Orders
        ADD FOREIGN KEY (P_Id)
        REFERENCES Persons(P_Id)
        GO
    

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

    ALTER TABLE Orders
        ADD CONSTRAINT fk_PerOrders
        FOREIGN KEY (P_Id)
        REFERENCES Persons(P_Id)
        GO
    

    To DROP a FOREIGN KEY Constraint

    ALTER TABLE Orders
        DROP CONSTRAINT fk_PerOrders
        GO
    



    SQL 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.

    SQL CHECK Constraint on CREATE TABLE

    The following SQL creates a CHECK constraint on the "PId" column when the "Persons" table is created. The CHECK constraint specifies that the column "PId" must only include integers greater than 0.

    CREATE TABLE Persons
        (
        	P_Id int NOT NULL CHECK (P_Id>0),
        	LastName varchar(255) NOT NULL,
        	FirstName varchar(255),
        	Address varchar(255),
        	City varchar(255)
        )
        GO
    

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

    CREATE TABLE Persons
        (
        	P_Id int NOT NULL,
        	LastName varchar(255) NOT NULL,
        	FirstName varchar(255),
        	Address varchar(255),
        	City varchar(255),
        	CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
        )
        GO
    

    SQL Check Constraints and Existing Values

    As with UNIQUE constraints, adding a CHECK constraint after a table is populated runs a chance of failure, because the database will check existing data for conformance. This is not optional behavior with a unique constraint, but it is possible to avoid the conformance test when adding a CHECK constraint using WITH NOCHECK syntax in SQL.

    CREATE TABLE Employees_2
        (
            EmployeeID int,
            Salary money
        )
        GO
    
        INSERT INTO Employees_2 VALUES(1, -1)
        GO
    
        ALTER TABLE Employees_2 WITH NOCHECK 
            ADD CONSTRAINT CK_Salary CHECK(Salary > 0)	
        GO
    
        INSERT INTO Employees_2 (EmployeeID, Salary) VALUES(2, NULL)
        GO
    

    SQL CHECK Constraint on ALTER TABLE

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

    ALTER TABLE Persons
        ADD CHECK (P_Id>0)
        GO
    

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

    ALTER TABLE Persons
        ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
        GO
    

    To DROP a CHECK Constraint

    ALTER TABLE Persons
        DROP CONSTRAINT chk_Person
        GO
    



    SQL DEFAULT Constraint

    The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

    CREATE TABLE Persons
        (
        	P_Id int NOT NULL,
        	LastName varchar(255) NOT NULL,
        	FirstName varchar(255),
        	Address varchar(255),
        	City varchar(255) DEFAULT 'Sandnes'
        )
        GO
    

    The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():

    CREATE TABLE Orders
        (
        	O_Id int NOT NULL,
        	OrderNo int NOT NULL,
        	P_Id int,
        	OrderDate date DEFAULT GETDATE()
        )
        GO
    

    SQL DEFAULT Constraint on ALTER TABLE

    ALTER TABLE Persons
        ALTER COLUMN City SET DEFAULT 'SANDNES'
        GO
    

    To DROP a DEFAULT Constraint

    ALTER TABLE Persons
        ALTER COLUMN City DROP DEFAULT
        GO
    

    Constraints:: http://odetocode.com/Articles/79.aspx

    Tips for using SQL Server 2008 constraints :: http://www.sswug.org/articles/viewarticle.aspx?id=53378

    Replied on Jan 17 2012 1:12AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    9

    Following are the CONSTRAINTS that are supported in SQL Server;

    • Primary Key Constraint
    • Foreign Key Constraint
    • Default Constraint
    • Unique Constraint
    • Check Constraint

    Primary Key Constraint

    Primary Key Constraint: Primary Keys constraints prevents duplicate values for columns and provides unique identifier to each column, as well it creates clustered index on the columns.

    1) Create Table Statement to create Primary Key

     a.Column Level
    
    USE AdventureWorks2008
    GO
    
    CREATE TABLE Products
    (
    ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,
    ProductName VARCHAR(25)
    ); 
    
    GO
    

    b.Table Level

    CREATE TABLE Products
    (
    ProductID INT,
    ProductName VARCHAR(25)
    CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
    );
    GO
    

    2) Alter Table Statement to create Primary Key

    ALTER TABLE Products
    ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
    GO
    

    3) Alter Statement to Drop Primary key

    ALTER TABLE Products
    DROP CONSTRAINT pk_products_pid;
    GO
    

    Foreign Key Constraint

    When a FOREIGN KEY constraint is added to an existing column or columns in the table SQL Server, by default checks the existing data in the columns to ensure that all values, except NULL, exist in the column(s) of the referenced PRIMARY KEY or UNIQUE constraint.

    1) Create Table Statement to create Foreign Key

    a. Column Level

        USE AdventureWorks2008
        GO
        CREATE TABLE ProductSales
        (
        SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY,
        ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
        SalesPerson VARCHAR(25)
        );
    
    GO
    

    b. Table Level

    CREATE TABLE ProductSales
    (
    SalesID INT,
    ProductID INT,
    SalesPerson VARCHAR(25)
    CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),
    CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
    );
    GO
    

    1) Alter Table Statement to create Foreign Key

    ALTER TABLE ProductSales
    ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
    GO
    

    2) Alter Table Statement to Drop Foreign Key

    ALTER TABLE ProductSales
    DROP CONSTRAINT fk_productSales_pid;
    GO
    

    Default Constraint

    Default constraint when created on some column will have the default data which is given in the constraint when no records or data is inserted in that column.

    1) Create Table Statement to create Default Constraint

    a. Column Level

    USE AdventureWorks2008
    GO
    CREATE TABLE Customer
    (
    CustomerID INT CONSTRAINT pk_customer_cid PRIMARY KEY,
    CustomerName VARCHAR(30),
    CustomerAddress VARCHAR(50) CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN'
    );
    GO
    

    b. Table Level : Not applicable for Default Constraint

    2) Alter Table Statement to Add Default Constraint

    ALTER TABLE Customer
    ADD CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN' FOR CustomerAddress
    GO
    

    3) Alter Table to Drop Default Constraint

    ALTER TABLE Customer
    DROP CONSTRAINT df_customer_Add
    GO
    

    UNIQUE Constraint

    You can create a UNIQUE constraint as part of the table definition when you create a table. If a table already exists, you can add a UNIQUE constraint, provided that the column or combination of columns that make up the UNIQUE constraint contains only unique values. A table can contain multiple UNIQUE constraints.

    If a UNIQUE constraint already exists, you can modify or delete it. For example, you may want the UNIQUE constraint of the table to reference other columns, or you may want to change the type of index clustering.

    ALTER TABLE MyTableName 
    ADD CONSTRAINT     UniqueContstraintName         
    UNIQUE NONCLUSTERED     (                 MyTableColumn    )
    

    I order to drop constraint;

    ALTER TABLE MyTableName DROP CONSTRAINT UniqueContstraintName;
    

    DEFAULT Constraint

    It is used to specify the default value for the column in case of NULL.

    ALTER TABLE MyTableName
    ADD NewCol VARCHAR(50)
    CONSTRAINT DF_MyTableName_NewCol DEFAULT '' NOT NULL
    GO
    

    CHECK Constraint

    CHECK constraints enforce domain integrity by limiting the values that are accepted by a column. They are similar to FOREIGN KEY constraints in that they control the values that are put in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression that is not based on data in another column. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range.

    CHECK constraints reject values that evaluate to FALSE. Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint. For example, suppose you place a constraint on an int column MyColumn specifying that MyColumn can contain only the value 10 (MyColumn = 10). If you insert the value NULL into MyColumn, the Database Engine inserts NULL and does not return an error.

    CREATE TABLE CheckTbl (col1 int, col2 int);
    GO
    CREATE FUNCTION CheckFnctn()
    RETURNS int
    AS 
    BEGIN
       DECLARE @retval int
       SELECT @retval = COUNT(*) FROM CheckTbl
       RETURN @retval
    END;
    GO
    ALTER TABLE CheckTbl
    ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );
    GO
    

    The CHECK constraint being added specifies that there must be at least one row in table CheckTbl. However, because there are no rows in the table against which to check the condition of this constraint, the ALTER TABLE statement succeeds.

    CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results. For example, consider the following statements executed on table CheckTbl.

    INSERT INTO CheckTbl VALUES (10, 10)
    GO
    DELETE CheckTbl WHERE col1 = 10;
    

    The DELETE statement succeeds, even though the CHECK constraint specifies that table CheckTbl must have at least 1 row.

    Replied on Jan 17 2012 6:53AM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    9

    The primary job of a constraint is to enforce a rule in the database. Together, the constraints in a database maintain the integrity of the database.Using constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.For instance, we have foreign key constraints to ensure all orders reference existing products. You cannot enter an order for a product the database does not know about.

    The constraint is very much needed for partition elimination and if we don’t get partition elimination , there is no use of going for Partition view or partition tables.

    Constraint is not only for data integrity but also for Performance. Constraints does help optimizer

    Data integrity rules fall into one of four categories:

    1. Entity
    2. Referential
    3. Domain
    4. User-Defined Integrity

    Classes of Constraints

    Six types of constraints supported by Microsoft SQL Server.

    1. NOT NULL
    2. CHECK
    3. UNIQUE
    4. PRIMARY KEY
    5. FOREIGN KEY
    6. DEFAULT

    Explanation

    1. NOT NULL
    A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

    2. CHECK
    A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

    3. UNIQUE
    A UNIQUE constraint uniquely identify each record in the table.NULL is allowed for one time.The unique key constraints are used to enforce entity integrity as the primary key constraints.

    4. PRIMARY KEY
    A PRIMARY KEY constraint uniquely identify each record in the table. (NULL is not allowed.).Only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

    5. FOREIGN KEY
    A FOREIGN KEY constraint is used to enforce referential integrity. You can control the actions SQL Server takes when you attempt to update or delete a key to which existing foreign keys point. You can control it by using the ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements.

    6. DEFAULT
    A DEFAULT constraint is used to populate fields that are left blank in the input source. it's a replacement for the use of NULL values that provide a great way to predefine common data elements.

    Example using Create Table

    CREATE TABLE ProductType(
        [PTypeId] INT NOT NULL
        	CONSTRAINT PK__ProductType_PTypeId PRIMARY KEY,
        [PTypeName]  VARCHAR(10) NOT NULL
            CONSTRAINT UNQ__ProductType__PTypeName UNIQUE
    )
    
    CREATE TABLE Product (
          [ProdID] INT NOT NULL
            CONSTRAINT PK__Product_ProdID PRIMARY KEY
          ,[ProdName]  VARCHAR(30) NOT NULL
            CONSTRAINT UNQ__Product__ProdName UNIQUE
        , [SalesPrice] decimal(12,2) NOT NULL
            CONSTRAINT CH__Product__SalesPrice
                CHECK (SalesPrice >= 1.0 AND SalesPrice <=5000.0)
        , [Quantity] INT NOT NULL
            CONSTRAINT CH__Product__Quantity
                CHECK (Quantity >= 1)
        , [PTypeId] INT NOT NULL
            CONSTRAINT FK__Product__ProductType FOREIGN KEY REFERENCES ProductType([PTypeId])
        , [DateEnter] datetime NOT NULL
            CONSTRAINT DF__Product_DateEnter DEFAULT GetDate()
        , [Posted] bit NOT NULL
        	CONSTRAINT DF__Product_Posted DEFAULT 0
    );
    
    --DROP Table
    DROP TABLE Product
    DROP TABLE ProductType
    

    Example using Alter Table

    CREATE TABLE ProductType(
        [PTypeId] INT NOT NULL,
        [PTypeName]  VARCHAR(10) NOT NULL
        )
    
    CREATE TABLE Product (
          [ProdID] INT NOT NULL
         ,[ProdName]  VARCHAR(30) NOT NULL
         ,[SalesPrice] DECIMAL(12,2) NOT NULL
         ,[Quantity] INT NOT NULL
         ,[PTypeId] INT NOT NULL
        , [DateEnter] DATETIME NOT NULL
        , [Posted] BIT NOT NULL
    );
    
     ALTER TABLE ProductType ADD CONSTRAINT PK__ProductType__PTypeId PRIMARY KEY(PTypeId)
    
     ALTER TABLE ProductType ADD CONSTRAINT UNQ__ProductType__PTypeName UNIQUE(PTypeName)
    
     ALTER TABLE Product ADD CONSTRAINT PK__Product_ProdID PRIMARY KEY(ProdID)
    
     ALTER TABLE Product ADD CONSTRAINT UNQ__Product__ProdName UNIQUE(ProdName)
    
     ALTER TABLE Product ADD CONSTRAINT CH__Product__SalesPrice
                CHECK (SalesPrice >= 1.0 AND SalesPrice <=5000.0)
    
     ALTER TABLE Product ADD CONSTRAINT CH__Product__Quantity
                CHECK (Quantity >= 1)
    
     ALTER TABLE Product ADD CONSTRAINT FK__Product__ProductType FOREIGN KEY(PTypeId) REFERENCES ProductType([PTypeId])
    
     ALTER TABLE Product ADD CONSTRAINT DF__Product_DateEnter DEFAULT GetDate() for DateEnter
    
     ALTER TABLE Product ADD CONSTRAINT DF__Product_Posted DEFAULT 0 for Posted
    
     --DROP Table
     DROP TABLE ProductType
     DROP TABLE Product
    

    Script to find constraint for table Product and ProductType

    SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
    SCHEMA_NAME(schema_id) AS SchemaName,
    OBJECT_NAME(parent_object_id) AS TableName,
    type_desc AS ConstraintType
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT'
    AND OBJECT_NAME(parent_object_id)='Product'
    OR OBJECT_NAME(parent_object_id)='ProductType'
    ORDER BY OBJECT_NAME(parent_object_id)
    

    you can use a UDF as a CHECK constraint for a column. Here's a simple example:

    --Create Function
    CREATE FUNCTION dbo.SimpleUDFCheckConstraint(@c1 AS INT) RETURNS BIT AS
    BEGIN
    RETURN    CASE  WHEN @c1 IN (SELECT Number FROM master..spt_values WHERE Type = 'P') 
    THEN 1  ELSE 0  END
    END
    GO
    
    --Create Table
    CREATE TABLE t
    (
    c1 INT NOT NULL
    CHECK (dbo.SimpleUDFCheckConstraint(c1) = CAST(1 AS BIT))
    )
    
    INSERT INTO t(c1) VALUES('1')
    INSERT INTO t(c1) VALUES('-1')
    INSERT INTO t(c1) VALUES('0')
    
    SELECT *  FROM t
    
    --Drop Table and Function
    
    DROP TABLE t
    DROP FUNCTION dbo.SimpleUDFCheckConstraint
    
    The INSERT with -1 will fail.
    

    Disable CHECK Constraint – Enable CHECK Constraint
    Foreign Key and Check Constraints are two types of constraints that can be disabled or enabled when required. This type of operation is needed when bulk loading operations are required or when there is no need to validate the constraint. The T-SQL Script that does the same is very simple.

    USE AdventureWorks
    GO
    -- Disable the constraint
    ALTER TABLE HumanResources.Employee
    NOCHECK CONSTRAINT CK_Employee_BirthDate
    GO
    -- Enable the constraint
    ALTER TABLE HumanResources.Employee
    WITH CHECK CHECK CONSTRAINT CK_Employee_BirthDate
    GO
    

    http://blog.sqlauthority.com/2007/09/16/sql-server-2005-list-all-the-constraint-of-database-find-primary-key-and-foreign-key-constraint-in-database

    http://blog.sqlauthority.com/2009/11/12/sql-server-disable-check-constraint-enable-check-constraint

    Replied on Jan 17 2012 8:15AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    6

    Constraints let you define the way the Database Engine automatically enforces the integrity of a database.

    NOT NULL specifies that the column does not accept NULL values.

    CHECK constraints enforce domain integrity by limiting the values that can be put in a column. CREATE TABLE custsample ( custid int PRIMARY KEY, custname char(50), custaddress char(50), custcreditlimit money, CONSTRAINT chkid CHECK (custid BETWEEN 0 and 10000 ) )

    UNIQUE constraints enforce the uniqueness of the values in a set of columns.

    PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table. CREATE TABLE partsample (partnmbr int PRIMARY KEY, partname char(30), partweight decimal(6,2), part_color char(15) );

    FOREIGN KEY constraints identify and enforce the relationships between tables. CREATE TABLE orderpart (ordernmbr int, partnmbr int FOREIGN KEY REFERENCES partsample(partnmbr) ON DELETE NO ACTION, qtyordered int); GO

    Constraints can be column constraints or table constraints. A column constraint is specified as part of a column definition and applies only to that column. The constraints in the previous examples are column constraints. A table constraint is declared independently from a column definition and can apply to more than one column in a table. Table constraints must be used when more than one column must be included in a constraint. CREATE TABLE factoryprocess (eventtype int, eventtime datetime, eventsite char(50), eventdesc char(1024), CONSTRAINT eventkey PRIMARY KEY (eventtype, eventtime) )

    Ref- BOL

    Replied on Jan 18 2012 12:39PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    3

    Constraints define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity.

    1. NOT NULL specifies that the column does not accept NULL values.

    2. CHECK constraints enforce domain integrity by limiting the values that can be put in a column

    CREATE TABLE citydata ( cityid int PRIMARY KEY, cityname varchar(50) CONSTRAINT chkid CHECK (city_id BETWEEN 1 and 200 ) )

    3. UNIQUE constraints enforce the uniqueness of the values in a set of columns. In a UNIQUE constraint, no two rows in the table can have the same value for the columns

    4. PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table

    CREATE TABLE citydata (cityid int PRIMARY KEY, city_name varchar(50));

    5. FOREIGN KEY constraints identify and enforce the relationships between tables. A foreign key in one table points to a candidate key in another table.

    Replied on Jan 19 2012 2:42AM  . 
    sk2000
    515 · 0% · 73

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.