Change table check constraint to column check constraint?

Change table check constraint to column check constraint?

Post by b_h.. » Tue, 26 Sep 2000 04:00:00



After our SQL 6.5 database was wiz'd into SQL 7.0 format, I noticed that many
(but not all) of the column check constraints were converted to table check
constraints.  These really are simple data-entry checks, and logically belong
with the column.

Is there a way to convert them back to column check constraints without
having to recreate the table?  I can't find any syntax in ALTER TABLE to add
a column check constraint.

Why do I care?  It's either change the ERWin schema or fix the database, and
frankly, I think ERWin is right this time.

TIA
--
Bobb Head

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Change table check constraint to column check constraint?

Post by BP Margoli » Tue, 26 Sep 2000 04:00:00


Bobb,

From a pure logic viewpoint, any column check constraint can be "promoted"
to a table check constraint, whereas the reverse is not true.

Unfortunately, the actual CREATE TABLE text is not stored by SQL Server, so
SQL Server automatically promotes all column check constraints to table
check constraints internally. When you ask for a Generate Script ... from
SQL Server 7.0, all column check constraints are scripted as table check
constraints. Even if you recreate the table in SQL Server 7.0, if you do
Generate Script on the newly recreated table, you should see all the column
check constraints promoted to table check constraints.

I have to admit to a puzzlement when you say that not all of the column
check constraints were promoted. My experience is that all are promoted. Is
it possible for you to double-check on that point?

----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> After our SQL 6.5 database was wiz'd into SQL 7.0 format, I noticed that
many
> (but not all) of the column check constraints were converted to table
check
> constraints.  These really are simple data-entry checks, and logically
belong
> with the column.

> Is there a way to convert them back to column check constraints without
> having to recreate the table?  I can't find any syntax in ALTER TABLE to
add
> a column check constraint.

> Why do I care? It's either change the ERWin schema or fix the database,
and
> frankly, I think ERWin is right this time.

> TIA
> --
> Bobb Head

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

Change table check constraint to column check constraint?

Post by b_h.. » Wed, 27 Sep 2000 04:00:00


BP, I did double-check, and I've found a pattern.  The column checks
that were "promoted" contain are complex (they contain OR clauses), and
those that remained as column checks contain one simple expression.
I've included some sample queries below with some observations of what
seems to be happening to the database catalog.

Sadly, knowing this still does not help me "unpromote" the affected
constraints, which remains the core of this question.  I am reluctant
to meddle with the system tables without a very, very fine reason and
absolute confidence that it is will do exactly what I intend and only
what I intend.

On to the show...

EXHIBIT A -- wherein a simple check constraint is shown to be
associated with a column, but a complex check constraint has been
"promoted" to a table check by the SQL 7 wiz'r.

select sc.constid, sc.colid, sc.status, text = substring(scm.text,1,50)
from sysconstraints sc, sysobjects so, syscomments scm
where sc.constid = so.id and so.id = scm.id
  and so.type = 'C'  -- and filter the results by hand...

constid     colid  status     text
----------- ------ ---------- ------------------------------------------
1742629251  8      133140     ([Longitude] < 0)
2142630676  0      133156     ([InterviewTime] >= 0 and [InterviewTime]
<= 2400)

EXHIBIT 2 -- wherein a table is created with both a column and a table
check constraint to observe how the database catalog represents these
facts.  Notice that in sysobjects colid and status are different.  I
believe that sysconstraints is a view in 7.0, so the differences in
sc.info and sc.status are reflections of the differences in sysobjects.

create table zzzBhFoo (
    foobar char(30) check (foobar in ('foo', 'bar')),
    threads char(30),
    check (threads in ('cotton', 'wool', 'linen'))
)

select sc.* from sysconstraints sc, sysobjects so
where sc.id = (select id from sysobjects where name = 'zzzBhFoo')
  and sc.constid = so.id and so.type = 'C'

constid     id          colid  spare1 status      actions     error
----------- ----------- ------ ------ ----------- -----------
-----------
215007847   199007790   1      0      133140      4096        0
231007904   199007790   0      0      133156      4096        0

select name = substring(name, 1, 25), id, info, status, parent_obj
from sysobjects
where id in (select constid from sysconstraints
    where id = (select id from sysobjects where name = 'zzzBhFoo'))

name                      id          info   status      parent_obj
------------------------- ----------- ------ ----------- -----------
CK__zzzBhFoo__foobar__0CD 215007847   1      6           199007790
CK__zzzBhFoo__0DC4E6A0    231007904   0      4           199007790

EXHIBIT III -- wherein we see that SQL 7 represents an in-ie as an
or-ie, to wit, an IN clause is represented as one or more OR clauses.

select sc.constid, sc.colid, sc.status, text = substring(scm.text,1,50)
from sysconstraints sc, sysobjects so, syscomments scm
where sc.constid = so.id and so.id = scm.id
  and so.type = 'C' -- and filter the results by hand...

constid     colid  status      text
----------- ------ ----------- ----------------------------------------
215007847   1      133140      ([foobar] = 'bar' or [foobar] = 'foo')
231007904   0      133156      ([threads] = 'linen' or ([threads] =
'wool' or [th

--
Bobb Head



> Bobb,

> From a pure logic viewpoint, any column check constraint can be
"promoted"
> to a table check constraint, whereas the reverse is not true.

> Unfortunately, the actual CREATE TABLE text is not stored by SQL
Server, so
> SQL Server automatically promotes all column check constraints to
table
> check constraints internally. When you ask for a Generate Script ...
from
> SQL Server 7.0, all column check constraints are scripted as table
check
> constraints. Even if you recreate the table in SQL Server 7.0, if you
do
> Generate Script on the newly recreated table, you should see all the
column
> check constraints promoted to table check constraints.

> I have to admit to a puzzlement when you say that not all of the
column
> check constraints were promoted. My experience is that all are
promoted. Is
> it possible for you to double-check on that point?

> ----------------------------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> can be cut and pasted into Query Analyzer is appreciated.




- Show quoted text -

> > After our SQL 6.5 database was wiz'd into SQL 7.0 format, I noticed
that
> many
> > (but not all) of the column check constraints were converted to
table
> check
> > constraints.  These really are simple data-entry checks, and
logically
> belong
> > with the column.

> > Is there a way to convert them back to column check constraints
without
> > having to recreate the table?  I can't find any syntax in ALTER
TABLE to
> add
> > a column check constraint.

> > Why do I care? It's either change the ERWin schema or fix the
database,
> and
> > frankly, I think ERWin is right this time.

> > TIA
> > --
> > Bobb Head


Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

Change table check constraint to column check constraint?

Post by Erland Sommarsk » Wed, 27 Sep 2000 04:00:00



>After our SQL 6.5 database was wiz'd into SQL 7.0 format, I noticed that
>many (but not all) of the column check constraints were converted to table
>check constraints.  These really are simple data-entry checks, and
>logically belong with the column.

>Is there a way to convert them back to column check constraints without
>having to recreate the table?  I can't find any syntax in ALTER TABLE to
>add a column check constraint.

>Why do I care?     It's either change the ERWin schema or fix the
>database, and frankly, I think ERWin is right this time.

I guess the m*of the story is: don't use the wizard. The wizard
is maybe good if you have a product which bought from someone else,
and haven't developed yourself.

But if you have developed the product you proably want to build the
database according to what you have in your version control system.
Using the wizard, means that you are prone to all scripting errors
that the 6.5 engine offers. And, yes, there are such errors. I've
seen 6.5 display and script complex CHECK constraints incorrectly -
but the actual implementation has been correct.

And what does the wizard do for you? Script out, bulk out, script in,
bulk in, and that's all. You need to change compatibility level
yourself. And what of this requires a wizard? Scripting out you
don't want to do, for scripting in you have your code in your VCS
(I hope!). And BCP isn't that difficult run.

--

 
 
 

1. Column check constraint vs table check constraint

If I create a column with a CHECK constraint (either via CREATE
TABLE or ALTER TABLE ADD newcol), the system tables record the
fact that the constraint is a column-level constraint.  For
example
     create table x (pk int identity primary key, x int not null
constraint CK_x_x CHECK(x between 1 and 9))
     sp_helpconstraint 'x'
The constraint is described as "CHECK on column x" in the 1st
column of the 2nd result set.

If the check expression changes -- e.g. if in the new world 10 is
valid -- the obvious (?only?) thing to do is to drop the
constraint and add it back again with the new expression:
     alter table x drop constraint CK_x_x
     alter table x add constraint CK_x_x CHECK(x between 1 and
10)
But now sp_helpconstraint shows the constraint as "CHECK Table
Level".

The syntax diagram for ALTER TABLE indicates that, unless we are
adding a new column, we can only add what it calls a
table_constraint.  The definition of a CHECK table_constraint
does not allow us to specify which column it is for.  The system
is not noticing that only one column is being referenced in the
CHECK expression, and is not associating the constraint with that
column.  That seems to be somewhere between a missing feature and
a bug.  If I had a way to tell the system explicitly which column
the CHECK applies to (e.g. by the system supporting the syntax
    CHECK (expression) FOR colname
as it does when it is a DEFAULT constraint created with ALTER
TABLE ADD CONSTRAINT), it would be a missing feature that it
doesn't notice that the expression only references one column.
However, as I apparently have no way to modify the CHECK
expression text without it losing its column-level-ness, it's
more like a bug.

Is there anything that I can do about this?  Any suggestions?

2. SQL Server Naming and Development Guidelines

3. Problem with Alter Table Constraint NO CHECK/CHECK?

4. Field Order In Tables

5. Changing Existing Column Defaults and Check Constraints

6. Sql Server 6 & Identity columns

7. Changing a table-level constraint to a column-specific constraint

8. Cell Colour Format Based on Two Criteria?

9. Table level column reference problem in Check constraint

10. Find check constraints given Table and Column

11. checking the violation of constraint with existing data (after enabling of the constraint)

12. Check Constraint vs. Foreign Key Constraint

13. check constraint or referential constraint