RULES AND DEFAULTS IN SQL SERVER

RULES AND DEFAULTS

CREATING DEFAULT: Default is one of the database objects used to declare default values globally.

Syntax:

CREATE DEFAULT DEFAULTNAME AS NUMERICVALUE/STRING VALUE

E.g.:

CREATE DEFAULT D1 AS ‘UNKNOWN’

BINDING THE DEFAULT TO COLUMN

Syntax:

 SP_BINDDEFAULT   DEFAULTNAME, ‘TABLENAME.COLUMN’

E.g.:

SP_BINDDEFAULT   D1, ‘DEPT.LOC’

SP_BINDEFAULT D1, ‘EMP.ENAME’

INSERT INTO DEPT (DEPTNO, DNAME) VALUES (10,ACCOUNTING’)

SELECT * FROM DEPT WHERE DEPTNO =10

            DEPTNO                     DNAME                      LOC

            10                                ACCOUNTING          UNKNOWN

INSERT INTO EMP (EMPNO, SAL, DEPTNO) VALUES (100, 5000,20)

SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE EMPNO =100

            EMPNO                      ENAME                      DEPTNO

            100                              UNKNOWN               20       

UNBINDING THE DEFAULT FROM THE BINDING COLUMN

Syntax:

 SP_UNBINDEFAULT  ‘TABLENAME..COLUMN’

SP_UNBINDEFAULT            ‘ DEPT.LOC’

SP_UNBINDEFAULT            ‘ EMP.ENAME’

DROPPING THE DEFAULT:

Syntax:

 DROP DEFAULT DEFAULTNAME

 DROP DEFAULT D1

CREATING RULES: Rule is just check constraint but it is placed on columns globally.

Syntax:

CREATE RULE RULENAME AS   EXPRESSION

E.g.:

CREATE RULE R1 AS @ X >=10 AND @ X <= 60

BINDING THE RULE:

            SP_BINDEFAULT   RULENAME, ‘ TABLE.COLUMN’

            SP_BINDEFAULT      R1, ‘DEPT.DEPTNO’

            INSERT INTO DEPT (DEPTNO) VALUES (70)

ERROR

UNBINDING THE RULES:

SP_UNBIND RULE ‘DEPT.DEPTNO’

DROPPING THE RULES:

DROP RULE RULENAME

DROP RULE    R1