Ad

How Do I Enforce Data Integrity Rules In My Database?

- 1 answer

I'm designing this collection of classes and abstract (MustInherit) classes…

class diagram

This is the database table where I'm going to store all this…

database table

As far as the Microsoft SQL Server database knows, those are all nullable ("Allow Nulls") columns.

But really, that depends on the class stored there: LinkNode, HtmlPageNode, or CodePageNode.

Rules might look like this...

rules table

How do I enforce such data integrity rules within my database?


UPDATE: Regarding this single-table design...

I'm still trying to zero in on a final architecture.

I initially started with many small tables with almost zero nullalbe fields.
Which is the best database schema for my navigation?

And I learned about the LINQ to SQL IsDiscriminator property.
What’s the best way to handle one-to-one relationships in SQL?

But then I learned that LINQ to SQL only supports single table inheritance.
Can a LINQ to SQL IsDiscriminator column NOT inherit?

Now I'm trying to handle it with a collection of classes and abstract classes.
Please help me with my .NET abstract classes.

Ad

Answer

Use CHECK constraints on the table. These allow you to use any kind of boolean logic (including on other values in the table) to allow/reject the data.

From the Books Online site:

You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. For the previous example, the logical expression is: salary >= 15000 AND salary <= 100000.

Ad
source: stackoverflow.com
Ad