Sql Server material 6

Primary Key Constraint.

-While Creating a primary key constraint we need to keep this in mind i.e. a table can contain only a single primary key present on it which can be present on a single column or multiple columns also.

Creating Primary Key in column level:

CREATE TABLE Bank(Custid int Constraint Cusid_PK Primary Key, Cname varchar(50), Bal decimal(7,2) Not Null)

Creating Primary Key in table level:

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Not Null, Constraint Cusid_PK Primary Key(Custid))

Creating a Composite Primary Key in table level:

CREATE TABLE BankDetails(CityCode varchar(10), BranchCode varchar(10), Constraint CC_BC_PK Primary Key(CityCode, BranchCode))

Check Constraint: If we want to check the values present in a column to be according to a specified value we use this constraint.

-If we want to restrict the Bal in the bank table should be some specified values then we can use the constraint as following:

            CHECK (Bal>=1000) –> Checking Bal should be greater than equal to 1000

CHECK (Bal BETWEEN 1000 AND 9999) -> Checking Bal should be within the range of 1000 and 9999

CHECK (BAL IN (3000, 5000, 7000)) -> Checking the Bal Should be within any of the three values only

Creating Check Constraint in column level:

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Constraint Bal_CK Check (Bal>=1000))

Creating Check Constraint in table level:

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2), Constraint Bal_CK Check(Bal BETWEEN 1000 AND 9999))

Default Value: The default value for any column if a not null constraint is not present on it is “NULL”, which can be changed by using the Default Clause while creating the table as following:

           

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Default 1000)

-In the above case if have not specified any value to the Bal column while inserting then it takes 1000 as default

            INSERT INTO Bank (Custid, Cname) VALUES (101, ‘Ravi’)

Identity Function: Generally for any column if we want to insert only unique values then we can hand over the task to the identity function, so that it takes the responsibility of inserting a unique value in to the column as following:

            Colname <dtype> [width] Identity [(Seed, Incr)]

            Seed – It is the starting value for the identity function.

            Incr – It is the difference between to subsequent values generated by the function.

-Both of them are optional, if not specified 1 and 1 are taken as values.

-When we use the identity function on a column we cannot explicitly insert any values into the column using the insert statement.

-A table can have only one identity column present in it.

-Generally we use this on Primary Key Columns.

CREATE TABLE Bank(Custid int identity(101, 1), Cname varchar(50), Bal decimal(7,2))

-In this case when we insert rows into the table then it automatically generates a identity value starting from 101.

            INSERT INTO Bank (Cname, Bal) VALUES (‘Raju’, 3500)

<<< Previous                                                                                                                               Next >>>