5

Does SQL Server 2008 support the CREATE ASSERTION syntax?

I haven't been able to find a straight answer on this.

Joe
  • 95
  • 1
  • 4

4 Answers4

5

No SQL Server 2008 does not support this syntax.

brendan
  • 29,308
  • 20
  • 68
  • 109
  • 2
    -1 Factually correct but unhelpful: there are alternatives to achieve the same result. – gbn Nov 09 '10 at 06:04
  • 4
    @gbn: actually, your alternatives don't achieve the same result. Also, the OP specified they just want a straight answer. – onedaywhen Nov 09 '10 at 08:15
4

No, you'd use a CHECK constraint or a TRIGGER instead, depending on complexity.

  • CHECK constraint: at the row level only
  • TRIGGER: across rows or using other tables
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 2
    But the issue here is that `CREATE ASSERTION` is at the schema level and SQL Server doesn't have anything at that level. Triggers are procedural and not comparable to SQL-92's set based paradigm. – onedaywhen Nov 09 '10 at 08:20
  • @onedaywhen: it does depend on what OP really wants to achieve, no? CREATE ASSERTION may be overkill where it can be addressed via CHECK OR TRIGGER. The issue is not "no" but how can we help the OP. Unless you're here to score clever points instead. – gbn Nov 09 '10 at 17:57
  • "CREATE ASSERTION may be overkill where it can be addressed via CHECK OR TRIGGER." -- Can you post a proof for this assertion (I chose my words carefully), please? – onedaywhen Nov 10 '10 at 09:54
  • 2
    "it does depend on what OP really wants to achieve, no?" -- They said they just wanted a straight answer, yes? – onedaywhen Nov 10 '10 at 09:55
4

SQL Server 2008 does not support CREATE ASSERTION.

In fact, no current SQL products support CREATE ASSERTION properly. Sybase SQL Anywhere supports it but reportedly has problems allowing constraints to sometimes be violated. Rdb did support it when it was looked after by DEC (who apparently were influential in the early days of the SQL standards, probably the reason why CREATE ASSERTION is in the standard in the first place) but that product is no more.

STOP PRESS: according to their documentation, HyperSQL (HSQLDB) supports assertions (though I'm left wondering why they don't shout about it on their list of features).

Community
  • 1
  • 1
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • 2
    i think the hsql documentation is flawed. i just tried the standard SQL syntax (validated by Mimer online), and hsql gave me: Unexpected token: ASSERTION in statement [CREATE ASSERTION] -- this is on Fedora, HSQL Database Engine v. 1.8.1, SqlTool v. 333. (SqlFile processor v. 354) – pestophagous Feb 22 '13 at 05:12
0

I was looking for this recently and found that http://hsqldb.org/doc/guide/databaseobjects-chapt.html#dbc_assertions says "An ASSERTION is a top-level schema object. It consists of a that must not be false (can be unknown). HyperSQL does not yet support assertions."

Lawrence Cohan
  • 9
  • 1