Very Computer

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.

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.

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

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.

--