Compare SQL Server, Oracle, and DB2 constraints to establish statements!

The constraint is used to force the row data to meet specific business rules (the data type is to force the column data to meet the rules)

There are five types of constraints: • not null • unique • primary key • Foreign key • check the not null constraint on SQL Server: • Create Table u_emp (• empno bigint, • ename varchar (10) Not null, • job varchar (9), • Mgr bigint, • hiredate date, • Sal decimal (7,2), • comm decimal (7,2 ), • deptno decimal (7,2) not null); not null constraints on Oracle: Create Table EMP ( Empno number (4 ), Ename varchar2 (10) Not null, Job varchar2 (9 ), Mgr number (4 ), Hiredate date, Sal number (7,2 ), Comm number (7, 2 ), Deptno number (7,2) not null );

Not null constraints on DB2: Create Table u_emp (empno integer, ename varchar (10) Not null, job varchar (9), Mgr integer, hiredate date, Sal decimal ), comm decimal (), deptno decimal () not null); SQL Server unique constraints: Create Table u_dept (deptno integer, dname varchar (14 ), loc varchar (13), constraint dept_dname_uk unique (dname); unique constraints on Oracle: Create Table dept ( Deptno number (2 ), Dname varchar2 (14 ), Loc varchar2 (13 ), Constraint dept_dname_uk unique (dname ));Unique constraints on DB2: Create Table u_dept (deptno integer, dname varchar (14) not null, Loc varchar (13), constraint dept_dname_uk unique (dname )); PK constraints on SQL SERVER: Create Table p_dept (deptno integer, dname varchar (14), Loc varchar (13), constraint dept_dname_uk1 unique (dname ), constraint dept_deptno_pk1 primary key (deptno); PK constraints on Oracle Create Table dept ( Deptno number (2 ), Dname varchar2 (14 ), Loc varchar2 (13 ), Constraint dept_dname_uk unique (dname ), Constraint dept_deptno_pk primary key (deptno ));PK constraints of DB2 and: Create Table p_dept (deptno integer not null, dname varchar (14) not null, Loc varchar (13), constraint dept_dname_uk1 unique (dname ), constraint dept_deptno_pk1 primary key (deptno); FK constraints on SQL SERVER: Create Table f_emp (empno integer, ename varchar (10) Not null, job varchar (9), Mgr integer, hiredate date, Sal decimal (7,2), comm decimal (7,2), deptno integer not null, constraint emp_deptno_fk foreign key (deptno) References p_dept (deptno); FK constraints on Oracle: Create Table EMP ( Empno number (4 ), Ename varchar2 (10) Not null, Job varchar2 (9 ), Mgr number (4 ), Hiredate date, Sal number (7,2 ), Comm number (7, 2 ), Deptno number (7,2) not null, Constraint emp_deptno_fk foreign key (deptno) References dept (deptno ));   FK constraints on DB2:

Create Table f_emp (empno integer, ename varchar (10) Not null, job varchar (9), Mgr integer, hiredate date, Sal decimal (7,2), comm decimal (7,2 ), deptno integer not null, constraint emp_deptno_fk foreign key (deptno) References p_dept (deptno); FK constraints have the following features: 1. Foreign key: 2 is required for table-level definition. References: Specifies the primary table and its primary key column 3. On Delete cascade: Check constraint on SQL Server for cascading deletion options: Create Table Test (deptno bigint constraint emp_deptno_ck check (deptno

Between 10 and 99) Check constraints on Oracle: Create Table Test (deptno number (2) Constraint emp_deptno_ck check (deptno Between 10 and 99 ))Check constraints on DB2: Create Table Test (deptno number (2) Constraint emp_deptno_ck check (deptno Between 10 and 99 ))