SQL Homework – November 2018 – Constraints

Leave a comment

November 5, 2018 by Kenneth Fisher

You’ve created tables before but how about constraints? Constraints allow a finer level of control over what data is allowed into a given field or combination of fields. Basically, you are putting in some form of business logic. The benefit is that this logic remains in place regardless of how the data is added to the table(s). So, not just when it’s added via an application or given import process. You don’t want to add too much business logic to your database (IMO) but constraints are a pretty lightweight.

There are a bunch of different types of constraints. In order to try out all of them, you’ll need to create two tables. Here is the information you’ll need to create the tables, and the requirements for each. See if you can create each of the different types of constraints required.

-- Employee
Id (Auto incrementing integer, primary key)
EmployeeId (10 character string, cannot be null, must be unique)
FirstName (Variable length string, cannot be null)
LastName (Variable length string, cannot be null)
EmploymentStatus (Variable length string, cannot be null, 
		Can only contain 'Employed', 'Not employed'
		defaults to 'Employed')

-- Dependent
Id (Auto incrementing integer, primary key)
EmployeeId (Value must exist in the Employee table. 
		Can not be null)
FirstName (Variable length string, cannot be null)
LastName (Variable length string, cannot be null)

Now, if you want to get really fancy have the LastName of the Dependent default to the last name of the referenced Employee. Hint: This will require going beyond constraints.

To “pass” all you have to do is get the tables created with the requirements in place. However, for extra credit, try doing this once by creating the tables, then adding the constraints after the fact. Then drop and recreate the tables, but this time create the tables and add the constraints in the same command.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,465 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: