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

Answer 1

The order Header MUST have a reference to the customer or you will have mismatched orders-----you will not be able to reference the order to a customer.

HOWEVER, it is possible to have no employee for the order if it came from the web. but in this case, you would make the online store an employee.

Answer 2

I did not quite understand your problem but I will try to answer it anyway. Every database system will allow you to set the EmpNo attribute in the OrderTbl relation to be nullable, because the primary key of this relation is OrdNo. If you do so, this will mean that you are allowed to have orders that do not have any employee associated. If this is your case, then let it to be nullable.

Otherwise, and in the case that you also want that every order has a customer (CustNo not nullable too), I would rather set a primary key composed by CustNo and EmpNo and remove the OrdNo attribute.

I hope this answers your question.

Regards,
Victor

Answer 3

A foreign key has to contain a value from the parent table.column or null. So, yes it could be null because that's what foreign keys allow. If you don't want it to be null you need to add the NOT NULL constraint. The only constraints which enforce not null are the not null constraint and the primary key constraint.