Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

DEFAULT constraint

The DEFAULT constraint provides a default value to a column when an INSERT statement doesn't explicitly assign a particular value. 

if no other value is specified explicitly, default value will be added to column by sql server data engine.


Create a default constraint using SQL Server management studio:

1.        In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design.

 

2.  Click on the column where you would like to create the DEFAULT constraint

3.  Enter the appropriate value in the Default Value or Binding cell of the properties page


4.  Check Default Constraint:


 

Default constraint using T-SQL:

How Create Create default constraint?

Column Level:

USE [WebShop]
GO
CREATE TABLE [dbo].[User](
     [ID] [int] IDENTITY(1,1) NOT NULL Primary Key,
     [ManagerId] [int] NOT NULL CONSTRAINT Df_ManageId DEFAULT 1,
     [UserType] [tinyint] NULL,
     [LoginName] [nvarchar](50) NULL Unique,
     [Password] [nvarchar](8) NULL,
     [CreatedDate] [datetime] NULL,
     [UpdatedDate] [datetime] NULL,
     [DeletedDate] [datetime] NULL
     )
 GO

Table Level:Not applicable

How To ADD default constraint using Alter?

ALTER TABLE [User]
ADD CONSTRAINT Df_ManageId DEFAULT 1 FOR ManagerId
GO

How to  DROP default constraint ?

ALTER TABLE [User]

DROP CONSTRAINT Df_ManageId

GO

 

 

FOREIGN KEY constraints

Foreign key is a column or field in current table and primary key of another table, the foreign key points to another table using primary key of that table.

Points to remember:

1.  Ensure value added in foreign key column exists in primary key table, otherwise a foreign key violation      error message is returned.
2.  While adding foreign key specify NOT NULL on all the participating columns, otherwise a foreign key violation error message is returned.
3.  FOREIGN KEY constraints can reference another column in the same table. This is referred to as a self-reference
4.  A column of type varchar(max) can participate in a FOREIGN KEY constraint only if the primary key it references is also defined as type varchar(max).
5.  The Database Engine does not have a predefined limit on either the number of FOREIGN KEY constraints, but we recommend using maximum 253 constraints.
Let’s explain with example:

Categories Table:


Products table:


 

In Products table CategoryId is Foreign key, in categories table CategoryId is primary Key, Products table CategoryId pointing to the categories table CategorId.

Foreign Key constraints using SQL Server Management Studio Table Designer:

To create the FOREIGN KEY constraint, right-click the field or upper pane of Table Designer and then click Relationships. When the Foreign Key Relationships dialog box appears, click Add to create the new constraint.


Modify the Tables and Columns Specification property group, which identifies the referenced and referencing columns in the foreign key. Select the property group listing and then click its associated browse button.

When the Tables and Columns dialog box appears, select the Categories table as the primary key table and the CategoryId column from that table. For the Products table, which is the foreign key table, select the CategoryId column.

    Modify the name of the foreign key, if desired (optional).


Now Click OK to close the Tables and Columns dialog box. The Foreign Key Relationships dialog box should reflect your changes, as shown in Figure


Check Foreign key created below table


Primary Key Constraints using T-SQL:

1.  In Object Explorer, connect to an instance of Database Engine.
2.  On the Standard bar, click New Query.
3.  Copy and paste the following examples into the query window and click Execute.

Create Foreign Key Example:

a.    Column Level

USE [WebShop]
GO
CREATE TABLE [dbo].[Products]
(
            [ProductID] [int] IDENTITY(1,1) NOT NULL Primary Key,
            [ProductName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](max) NOT NULL,
            [ImagePath] [nvarchar](max) NULL,
            [UnitPrice] [float] NULL,
            --Column level foreign key ,pointing to categories table
            [CategoryID] [int] NULL CONSTRAINT [FK_Products_Categories] FOREIGN KEY   REFERENCES Categories(CategoryID)
 )
GO

 

b.      Table Level

 
USE [WebShop]
GO
CREATE TABLE [dbo].[Products]
(
            [ProductID] [int] IDENTITY(1,1) NOT NULL Primary Key,
            [ProductName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](max) NOT NULL,
            [ImagePath] [nvarchar](max) NULL,
            [UnitPrice] [float] NULL,
            [CategoryID] [int] NOT NULL,
  --Table level foreign key ,pointing to categories table
            CONSTRAINT [FK_Products_Categories] FOREIGN KEY([CategoryID]) REFERENCES Categories(CategoryID)
 )
GO

 

 Alter Table Statement to create Foreign Key Example

ALTER TABLE Products
ADD CONSTRAINT FK_Products_Categories FOREIGN KEY(CategoryID)REFERENCES Categories(CategoryID)
GO

 

Drop Foreign Key Example

ALTER TABLE Products
DROP CONSTRAINT FK_Products_Categories;
GO

 

Like operator

Description:

We frequently use marching patterns to search required information. In T-SQL we use like operator to compare a value to similar values using wildcard operators.

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. A pattern can include regular characters and wildcard characters.

            During pattern matching, regular characters must exactly match the characters specified in the character string. Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators.

Definition:

Like operator is used to search or compare a specified pattern in column values.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern [ ESCAPE escape_character ];

 

Wildcard Characters

There are four types of wildcard characters in SQL Server:

1.  Percent sign (%)
2.  Underscore (_)
3.  Bracket ([])
4.  Caret (^)
 

Percent sign (%):

Allows you to match any string of any length (including zero length)

Example:

 
--return all matching columns from products table where ProductName start with 'r'
SELECT * FROM Products WHERE ProductName LIKE 'r%'
 
--return all matching columns from products table where ProductName have the matching pattern 'en'
SELECT * FROM Products WHERE ProductName LIKE '%en%'
 
---return all matching columns from products table where ProductName name End with 'c'
SELECT * FROM Products WHERE ProductName LIKE '%c' 


Underscore (_):

Allows you to match on a single character

Example:

--return all matching columns from products table where ProductName three
characters start with 'hc'
SELECT * FROM Products WHERE ProductName LIKE 'hc_'  
--return all matching columns from products table where ProductName is twelve characters long End with 'es'
SELECT * FROM Products WHERE ProductName LIKE '__________lt'
 
--return all matching columns from products table where ProductName start and end with any character whereas the middle character must be 'y'.
SELECT * FROM Products WHERE ProductName LIKE  '__L __'


Bracket ([]):

Allows you to match on any character in the [ ] brackets (for example, [abc] would match on a, b, or c characters)

Example:

--return all matching columns from products table where ProductName begin with
'R' or 'C'
SELECT * FROM Products WHERE ProductName LIKE'[rc]%'
 
--return all matching columns from products table where ProductName begin with 'a' or 'f' and end with '5'.
SELECT * FROM Products WHERE ProductName LIKE'[af]%5' 
 
--return all matching columns from products table where ProductName begin with 'H' and hold an 'C' in the second place.
SELECT * FROM Products WHERE ProductName LIKE'H[c]%'


Caret (^):

Allows you to match on any character not in the [^] brackets (for example, [^abc] would match on any character that is not a, b, or c characters)

Example:

--return all matching columns from products table where ProductName begin with
'R' and does not contain an 'H'  as the second place
SELECT * FROM Products WHERE ProductName LIKE 'R[^H]%'