Question

If you had to create a new table in your database called EMPLOYEE and load data into it using SQL commands below.

CREATE TABLE EMPLOYEE

(

              EMP_ID INT PRIMARY KEY,

              EMP_FNAME VARCHAR(200) NOT NULL,

              EMP_LNAME VARCHAR(200) NOT NULL,

              EMP_CITY VARCHAR(500) NULL ,

              EMP_STATE VARCHAR(20) NULL

)

1. INSERT INTO EMPLOYEE VALUE (1, ‘Kevin’, ‘Lailnay’, NULL, ‘PA’)
2. INSERT INTO EMPLOYEE VALUE (2, ‘Sarita, ‘Kawn, ‘Princeton’,’NJ’)
3. INSERT INTO EMPLOYEE VALUE (2, ‘Gloria, ‘Pitts, ‘Allentown, ‘PA’)
4. INSERT INTO EMPLOYEE VALUE (3, ‘Jose, ‘Hawillian, ‘Buffalo, ‘NY’)
5. INSERT INTO EMPLOYEE VALUE (NULL, ‘Jose, ‘Hawillian, ‘Brooklyn, ‘NY)

Out of 5 INSERT commands above, which SQL INSERT commands will violate Entity Integrity? Copy and Paste those SQL commands.

And explain in detail why the Entity integrity is violated for each of those rows?

Answer 1

INSERT INTO EMPLOYEE VALUE (NULL, ‘Jose, ‘Hawillian, ‘Brooklyn, ‘NY)

The above insert statement violates the Entity Integrity constraint.

As per the Entity Integrity constraint,the primary key in a table of the database cannot be NULL.The above insert statement clearly violates the condition as EMP_ID value which is the primary key is given NULL.

INSERT INTO EMPLOYEE VALUE (2, ‘Sarita, ‘Kawn, ‘Princeton’,’NJ’)

INSERT INTO EMPLOYEE VALUE (2, ‘Gloria, ‘Pitts, ‘Allentown, ‘PA’)

The above two INSERT statements are having duplicate primary key value of 2.It is also a violation but does not come under Entity Integrity constraint.It comes under Primary Key constraint of category Key constraints