SQL Homework – November 2018 – Constraints
2November 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.
SQL Studies 
Seems the default constraint isn’t necessary as the check constraint will cover it.
CREATE TABLE Employee
(id INT IDENTITY PRIMARY KEY CLUSTERED,
EmployeeID VARCHAR(10) NOT NULL UNIQUE,
Firstname VARCHAR(100) NOT NULL,
Lastname VARCHAR(100) NOT NULL,
EmploymentStatus VARCHAR(100) NOT NULL Check ( EmploymentStatus = ‘Employed’ OR EmploymentStatus = ‘Not Employed’) DEFAULT ‘Employed’
)
CREATE TABLE Dependent
( id INT IDENTITY PRIMARY KEY CLUSTERED,
employeeID VARCHAR(10) NOT NULL REFERENCES dbo.employee(EmployeeID),
Firstname VARCHAR(100) NOT NULL,
Lastname VARCHAR(100) NOT NULL,
)
Actually it won’t. You won’t be able to do an insert without specifying data for that column unless you have a default.