HuberBlog ::Jason Huber

A question I hear a lot and a problem I see a lot is related to Foreign Keys and the constraints that are placed on the field when it is a Foreign Key.

There are several types of constraints that we will place on fields when we are creating our database. Things such as NOT NULL, DEFAULT (s), PRIMARY KEYS and FOREIGN KEYS.

What each one means is sometimes obvious and other times not so much.

I regularly add a Primary Key as not null when I am told it is made that way by the constrain anyway. It is redundant to list it twice I am told, but I do it anyway.

Not null is just that – the field cannot be null. You will never see a record with that field NULL of a value. You cannot alter a table making a field NOT NULL if there wasnt a default on the field since there is a chance that the field might already be null.

DEFAULT is pretty straight forward as well. You choose to provide a default value to a field in the case that the user doesnt enter it. It is just about as effective as not null, but it will also fill in the value for you.

Primary Key is the most important constraint we will use. It applies NOT NULL and UNIQUE (a constraint I didnt mention because it is too obvious). The importance of PK cant be understated. Your choice of a primary key will affect not only the type and configuration of data that can be placed into the table, but it will also affect other tables that need to have a relationship with the primary key’d table. Choosing a field based on an inherent key – a key made up of fields already in the table which make a unique id is a always a good idea. Now there are exceptions to the rule – SSN isnt always a good PK for security reasons for example.

Foreign Keys are fields that can be made not null but if they are entered must exist in the table to which they refer. Take ORDERS and CUSTOMERS. An order will have a FK to customers. It is probably also NOT NULL. If you enter an order, you must enter a customerid and the customerid you enter must exist in the customer table. If you enter a customerid which is not in the customers table then you will get a key constraint violation (that is the point).

Lets say that we wanted to allow customers to enter new orders before they were logged into the system, and thus we dont yet know their customerid. We would leave the FK in place but remove the not null constraint. This would allow an order with no customerid, but when a customerid is entered (upon checkout probably) it must be a valid one, passing foreign key checks.

I hope this answers some of the early question about constraints and keys. There are plenty of resources out there on keys and constraints, so take this post along with a good good search and apply it to what you are working on.

Normally what I see is constraints applied backwards. In this short post it would be like CUSTOMERS table having a FK to ORDERS. This would lead to a customer only being allowed one order. Then they would need a new customer id.

-Dont do it.