Hi everyone, I have a question that has been bugging me. Using SQL:?

Question

Looking at these tables, would be permissible for null values to be allowed for the foreign key Empno in the OrderTbl table? This column would reference back to the empno column in the employee table. Why or why not? I don't seem to understand and I'm trying to put this database together.

TABLE Customer has the following attributes
CustNo CHAR(8)
CustFirstName VARCHAR2(20)
CustLastName VARCHAR2(30)
CustCity VARCHAR2(30)
CustState CHAR(2)
CustZip CHAR(10)
CustBal DECIMAL(12,2)

CustNo is the PRIMARY KEY

TABLE Employee has the following attributes
EmpNo CHAR(8)
EmpFirstName VARCHAR2(20)
EmpLastName VARCHAR2(30)
EmpPhone CHAR(15)

EmpNo is the PRIMARY KEY


TABLE OrderTbl has the following attributes
OrdNo CHAR(8)
OrdDate DATE
CustNo CHAR(8)
EmpNo CHAR(8)

OrdNo is the PRIMARY KEY

The CustNo column references the Customer table and the EmpNo column references the Employee table.

Answer 1

To add my two cents..

Although you can place an order referencing a CustNo and a NULL for the EmpNO, would you want to do this?

This is a bussiness rule where you impose additional restrictions onto the data entry in the table. It would be reasonable to require an Employee Number if an employee is involved in placing the order like over the phone. In fact a special EmpNo could be used to identify a non employee assisted order placement ( a mail in order or a self serve counter sale) . So EmpNo 0 could be used to identify use of a self serve check out without direct employee involvement

Again these are bussiness rules which are tailored to specific needs to enshure valid and consistent data.

Answer 2

Foreign key allows NULL values if it
1. is not defined as NOT NULL
2. is not included into composite Primary Key