contraints outside of standard contraints? 2

[1DMF]

Hi,

I am working on our DB Schema which the boss likes to call the 'Bus Plan', yup , what happens if I'm hit by a bus!

part of the interface i'm using has a 'Constraints' section.

Looking at this link

http://www.mssqlcity.com/Articles/General/using_constraints.htm

It defines MS SQL as having the following contraints

Each of these categories of the data integrity can be enforced by the appropriate constraints. Microsoft SQL Server supports the following constraints:


PRIMARY KEY
UNIQUE
FOREIGN KEY
CHECK
NOT NULL

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

I was wondering.. When defining a field, you state if nulls are allowed, you provide its data type (which is / can include a CHECK constraint), you define a primary key and also any foreign key relationships.

It seems in the DbSchema software I'm using, the contraints section is for putting in propositional contraints eg. 'age > 14'.

Does anyone use these contraints?

Is it good practice to place additional propositional contraints on fileds in tables?

What happens if there is data already in a table that equates to false from the proposition and does it have to be a boolean algebra proposition that always equates to true?

Your guidance is appreciated.
1DMF




"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download

 

[gmmastros]

Does anyone use these constraints?

Yes.

Is it good practice to place additional propositional contraints on fields in tables?

Yes. If you have business rules regarding data, it's best to add constraints to enforce those rules. Ex: Men can't get pregnant. Think of database constraints as your last line of defense against crappy data.

What happens if there is data already in a table that equates to false from the proposition and does it have to be a boolean algebra proposition that always equates to true?

If you already data in the table that would violate the constraint, you can still add it (there's a NOCHECK clause you can use when adding the constraint). I recommend against this because this will cause the constraint to be "un-trusted", which in turn prevents SQL from optimizing queries in a certain way. If you already have "bad" data in your database, I recommend that you clean up the data prior to adding the constraint.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Thanks George.

I assume the syntax is in boolean algebra proposition where it must equal true (well unless you specifically used negation to flip the value) but you know what I mean?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download

 

[gmmastros]

Yes.

For example....

Create Table People(
      Name VarChar(20), 
      Gender Char(1), 
      IsPregnant Bit)

Alter 
Table  People 
Add    Constraint GenderType 
           CHECK (Gender In ('M','F'))

Alter 
Table  People 
Add    Constraint OnlyWomenCanBePregnant 
          CHECK((IsPregnant = 1 and Gender = 'F') Or (IsPregnant = 0))

-- The following succeed.
Insert Into People(Name, Gender, IsPregnant) Values('George','M', 0)
Insert Into People(Name, Gender, IsPregnant) Values('Mother Theresa','F', 0)
Insert Into People(Name, Gender, IsPregnant) Values('Jane','F', 1)
Insert Into People(Name, Gender, IsPregnant) Values('Pat',NULL, 0)


-- The following fail
Insert Into People(Name, Gender, IsPregnant) Values('Pat','X', 0)
Insert Into People(Name, Gender, IsPregnant) Values('John','M', 1)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Many thanks George.

I'm certainly getting to grips with all this with your valuable input.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download