Rules and Defaults

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

Syntax:

CREATE DEFAULT DEFAULTNAME AS NUMERICVALUES/STRING VALUE

Example: CREATE DEFAULT D1 AS 'UNKNOWN'

Binding the default for columns:

Syntax:

SP_BINDDEFAULT DEFAULTNAME,'TABLENAME.COLUMN'

Example:

SP_BINDDEFAULT D1,'DEPT.LOC'

SP_BINDDEFAULT D1,'EMP.ENAME'

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

SELECT * FROM DEPT WHERE DEPTNO=10

DEPTNO = 10

DNAME = ACCOUNTING

LOC = UNKNOWN

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

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

EMPNO = 100

ENAME = UNKNOWN

DEPTNO = 20

UNBINDING THE DEFAULT FROM THE BINDING COLUMN

Syntax:

SP_UNBINDEFAULT 'TABLEENAME.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 column globally.

Syntax:

CREATE RULE RULENAME AS EXPRESSION

Example:

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

Rules and Defaults