Enumeration types, switch statements and extra data and logic

An enum is basicly a collection of related choices or states. For example you can have an enum State { active, inactive }.
One of the main strenghts of an enum is that it can be used in a switch statement, thus eliminating the use of multiple if statements:
switch(state)
{
    case State.active: deactivate(); break;
    case State.inactive: activate(); break;
}

A switch statement only work with constant expressions, meaning code that is always compiled the same way, like numbers, chars and enums.

In most languages an enum is basicly nothing more than a single int. You can't specify that the State.active always has the color green, and the State.inactive the color red, or add methods to specify how comparison should work.

C#

In C# you can add some extra information to enums using attributes, and you can use extension methods and reflection to get some extra information, but even with these possibilities the enum still is quite dumb.

We can use an enumeration class to add extra information to our enum types (like described here: https://github.com/HeadspringLabs/Enumeration), but you can't use this enumeration class in a switch statement since the enumeration types are not constant expression.

C++

C++ has basicly the same problem. Since the newest version of C++ (C++11) typed enums are added, which makes the enum work like it does in C#, but it is still not possible to add logic or extra data to them.
An other nice addition to the newest C++ version is the constexpr keyword.
Using this keyword you can make your own types work in a switch statement, as long as they can convert to an int type:
struct State
{
    const int state;
    constexpr operator int() { return state; }
};
constexpr State active= { 1 };
switch(state)
{
    case active: ..
}
Unfortunatly  it is still not possible to do the following:
struct State
{
    static constexpr State Active = { };
};
So we still can't create the same type of enumeration classes as in C#, but we can come far with the following workaround I found:
struct StateType { /* the implementation of the type */ };
struct State
{
    static constexpr StateType Active = { };
    static constexpr StateType Inactive = { };
};

Conclusion

In conclusion, enums will not really change, but hopefully the creators/designers of programming languages will make the languages soon better to make it possible to use enumeration classes in switch statements like you can use enums.

Removing all Foreign keys from MSSQL

Today I had some problems with removing tables.
To clear all test data from a database we can use the TRUNCATE method to cleanup the table. Other solution is to clear all the foreign keys and then drop the database.


My database contains tables and a schema. When we are developing we want to reset our database. So we use a sql script where we drop all the tables of a schema and then the database itself.


When I run the query I got this error message:


fk is not a constraint. could not drop constraint. see previous errors

And after some changes in my SQL:


could not drop object because it is referenced by a foreign key constraint


So why you wont delete. The table order seems to be right but it doesnt work?
We can get all the constraints from the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table.


For example you see here the Northwind database:





Ok, we can remove each constraint easy by adding in our SQL a DROP CONSTRAINT for each constraint. However, not easy when the database grows.


An other easy solution is check in the database, if found, we create a query and executes the query. Our solution will be:

WHILE(exists(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
 DECLARE @sql nvarchar(2000)
 SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
 + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
 FROM information_schema.table_constraints
 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
 EXEC (@sql)
END

In my case I want to remove only constraints from the given schemaname, so add an extra WHERE for TABLE_SCHEMA and we are done!