EDIT: SEE REPLY How about (SQL Server): CREATE TABLE #P ( name VARCHAR(100), count_empty BIT NOT NULL, count INT NULL,

EDIT: SEE REPLY How about (SQL Server): CREATE TABLE #P ( name VARCHAR(100), count_empty BIT NOT NULL, count INT NULL, count_key AS ISNULL(count,0), CHECK ((count_empty = 0 AND count IS NOT NULL) OR (count_empty = 1 AND count IS NULL)), PRIMARY KEY (name, count_empty, count_key) ); I think this should give the semantics you're looking for (if not the ergonomics). COUNT,SUM,AVG etc should work correctly on the "count" column (btw, I would consider using a different name ;P), and the check+primary key should enforce the constraints. You might add a before update trigger to set count to NULL when count_empty = 1, and hiding the actual table behind a view to hide the internals of the count_key column.