Question

Write the SQL code that will create only the table structure for a table named EMP_1. This table will be a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following table. Use EMP_NUM as the primary key. Note that the JOB_CODE is the FK to JOB so be certain to enforce referential integrity. Your code should also prevent null entries in EMP_LNAME and EMP_FNAME.

Answer 1

Required SQL code is:-

CREATE TABLE EMP_1

AS (SELECT EMP_LNAME VARCHAR(200) NOT NULL,

EMP_FNAME VARCHAR(200) NOT NULL,

EMP_NUM INT NOT NULL ,

CONSTRAINT PK_EMP_1 PRIMARY KEY (EMP_NUM)

FROM EMPLOYEE);

Referential integrity for the FK to JOB table:-

**Assuming that the JOB_CODE is a primary key for EMPLOYEE table as we need a primary key for referential integrity

CREATE TABLE JOB

( TempID int NOT NULL, Name nvarchar(50) ,

CONSTRAINT PK_EMPLOYEE PRIMARY KEY NONCLUSTERED (JOB_CODE) ,

CONSTRAINT FK_EMPLOYEE_ORDER FOREIGN KEY (JOB_CODE)

REFERENCES EMPLOYEE(JOB_CODE)

ON DELETE CASCADE

ON UPDATE CASCADE ) ;

** The ON DELETE AND ON UPDATE CASCADE command is used to ensure that the changes made in employee table are also accessed in JOB table automatically.