SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Homework – November 2018 – Constraints

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.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...