Identify Potential Constraints

Question

Identify Potential Constraints

neddyflanders

SSC Eights!

Points: 972
More actions
May 9, 2005 at 9:02 am

#164203

I am trying to identify good candidates to place Constraints on in a database. Besides going thorugh each of the tables manually does anybody have any suggestions to speed up the process?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Constraints depend on the integrity rules and business rules you are trying to enforce. Just looking at the table structure may provide some information you can use to infer these things, but it's better to start with the rules and apply them to the tables rather than take the tables and guess at the rules.

And then again, I might be wrong ...
David Webb

Lowell SSC Guru Points: 323518 More actions · Answer 2

i'm a huge fan of constraints.... here's the things i typically look for:

in our shop, Primary keys have a naming convention: they all end in either "ID" or "TBLKEY"

You probably have a similar convention...make sure every item named that way is either a primary key of a table, or has a foreign key to that primary key.

script example:

--IDENTIFY MISSING AND IMPLIED FOREIGN KEYS

--ALSO IDENTIFIES PRIMARY KEY COLUMN CANDIDATES THAT ARE MISSING THE PRIMARY KEY DEFINITION

select sysobjects.name as TblName,

sysobjects.id as Tblid,

syscolumns.name as ColName,

type_name(syscolumns.xtype) As DataType,

sysconstraints.*

--into #FKFinder

from sysobjects

inner join syscolumns on sysobjects.id=syscolumns.id

left outer join sysconstraints on sysobjects.id=sysconstraints.id and syscolumns.colid=sysconstraints.colid

where sysobjects.xtype='U'

and type_name(syscolumns.xtype)='int'

and syscolumns.name like '%TBLKEY'

and sysobjects.id not in(select parent_obj from sysobjects where xtype='PK')

AND CONSTID IS NULL

order by TblName,colname

search for all your CHAR(1) columns ; they most likely has a limited set of allowable values..for example Y or N; also a default of 'N' is often appropriate.

I like to add a check constraint where columnname = 'Y' or columnname='N'

example:

ALTER TABLE WHATEVER ADD COLUMN [ISPRIME] CHAR (1) NOT NULL DEFAULT ('N') CHECK ([ISPRIME] = 'N' OR [ISPRIME] = 'Y')

ALTER TABLE WHATEVER ADD COLUMN [SCHOOLGRADE] CHAR (1) NOT NULL CHECK ([SCHOOLGRADE] IN('A','B','C','D','F') )

money columns should have a default value of 0 to avoid all the nulltozero issues.

ALTER TABLE WHATEVER ALTER COLUMN [PAYAMT] MONEY DEFAULT (0)

for datetime fields, I like to add a check constraint where columnname > 01/01/1995 for example...that way an error is raised if a developer sticks a zero in the column, which is assumed to be 01/01/1900 ; this has saved me a lot of times with invalid dates that would have slipped through..

example:

ALTER TABLE WHATEVER ADD [ACTIONSENTDT] DATETIME NULL CHECK [ACTIONSENTDT] >'01/01/1995

Here is a script to find columns by data type:

select sysobjects.name as TblName,

syscolumns.name as ColName,

type_name(syscolumns.xtype) As DataType

--into #FKFinder

from sysobjects

inner join syscolumns on sysobjects.id=syscolumns.id

where sysobjects.xtype='U'

and type_name(syscolumns.xtype)='datetime'

--datatypes'bit','char','datetime','float','image','int','money','nvarchar','real','smallint','text','varchar')

--and syscolumns.name like '%TBLKEY'

order by TblName,colname

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

neddyflanders SSC Eights! Points: 972 More actions · Answer 3

Thanks Lowell, thats exactly what I was looking for.

Lowell SSC Guru Points: 323518 More actions · Answer 4

A while back in my shop i had to search all columns of type int.

In my case, many of the columns where actually foreign keys, but were not following the naming conventions, nor did they have the foreign key in place either;

In my example, the foreign key column should be the identical column name of the primary key it references.... so ParentTable.PTTBLKEY and ChildTable.PTTBLKEY should exist, and there should be a foreign key between the two; my problem was the column was crap like ChildTable.PTREF , which did not follow the naming comvention, and the database was missing the FK;

so my suggestion is to also review INT data columns for possible missing foreign keys that were left off.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!