tSQLt is a T-SQL framework running on SQL Server. SQL Server can shell out to the operating system using xp_cmdshell or even to call clr code via the clrruntime so, in theory, you could use tSQLt as a test framework to test anything, I would say limiting it to testing T-SQL code is probably best.
What we ideally should be looking to test with tSQLt is:
Stored procedures are the top item for testing, that is where people shove the majority of the business logic. Concerning business logic, I am not advocating T-SQL as a language for writing business logic, but sometimes having to have your code close to the data when the data store is huge is a requirement. Functions and views give us more flexibility when writing code.
Table constraints are an interesting one, if there was a critical constraint that meant life or death, then I would probably cover it with a unit test but would also expect integration tests to cover it. Choosing what to test is about choosing between the risk of that particular piece of code not being covered and the cost of development and maintenance - you need to look at your application and decide where to spend your development time.
The last action for this lesson is for you to write a unit test, it could be for the example database or for a piece of your own code. The test doesn't need to "boil the ocean" so go ahead and create the new test class and test in that class.