Microsoft SQL Server Compact 3.5 supports constraints to enforce the data integrity rules in a database.

Using Constraints

Constraints are added to columns using the ITableDefinitionWithConstraints interface. For foreign key constraints, the matchType element of the DBCONSTRAINTDESC structure should always be set to DBMATCHTYPE_FULL.

Examples

The following example shows how to use OLE DB to add a new UNIQUE constraint to the Extension column in the Employees table by using the ITableDefinitionWithConstraints::AddConstraint method.

// Declarations
HRESULT                         hr;
DBID                            TableName;  // name of table for new constraint
DBID                            ColumnList[1]; // name of column for new constraint
DBID                            ConstraintName; // name of new constraint
DBPROP                          dbprop[1];
DBPROPSET                       dbpropset[1];
DBCONSTRAINTDESC                rgConstraintDescs[1]; // Structure for constraint properties
IDBInitialize                   *pIDBInitialize       = NULL;        
IDBProperties                   *pIDBProperties       = NULL;        
IDBCreateSession                *pIDBCreateSession    = NULL;
ITableDefinitionWithConstraints *pITbleDefWithConstrt = NULL; // supports adding constraints

if (FAILED(hr = CoInitialize(NULL)))
{
    return;
}

VariantInit(&dbprop[0].vValue);        

// Create an instance of the OLE DB Provider
hr = CoCreateInstance(    CLSID_SQLSERVERCE, 0, CLSCTX_INPROC_SERVER,
    IID_IDBInitialize, (void**)&pIDBInitialize);
if(FAILED(hr))
{
    goto Exit;
}

// Initialize a property with name of database
// Open an exsiting database myDatabase
dbprop[0].dwPropertyID     = DBPROP_INIT_DATASOURCE;
dbprop[0].dwOptions         = DBPROPOPTIONS_REQUIRED;
dbprop[0].vValue.vt         = VT_BSTR;
dbprop[0].vValue.bstrVal = L"\\windows\\MyDB.sdf";

// Initialize the property set
dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
dbpropset[0].rgProperties     = dbprop;
dbpropset[0].cProperties     = sizeof(dbprop)/sizeof(dbprop[0]);

//Set initialization properties.
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, 
    
(void **)&pIDBProperties);
if(FAILED(hr))
{
    goto Exit;
}

// Sets properties in the Data Source and initialization property groups
hr = pIDBProperties->SetProperties(1, dbpropset); 
if(FAILED(hr))
{
    goto Exit;
}

// Initializes a data source object 
hr = pIDBInitialize->Initialize();
if(FAILED(hr))
{
    goto Exit;
}

// Get IDBCreateSession interface
hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession,
    (void**)&pIDBCreateSession);
if (FAILED(hr))
{
    //Send error-specific message and do error handling
    goto Exit;
}

// Create a session object. 
hr = pIDBCreateSession->CreateSession(NULL, IID_ITableDefinitionWithConstraints, 
    (IUnknown**) &pITbleDefWithConstrt);

// (This sample assumes that we have information about the Employees table
// database schema.)
// Prepare the table name DBID as Employees.
TableName.eKind          = DBKIND_NAME;
TableName.uName.pwszName = L"Employees";

// Prepare the list of columns that will receive the UNIQUE constraint. 
// In this case, just the Extension column. 
ColumnList[0].eKind = DBKIND_NAME;
ColumnList[0].uName.pwszName = L"Extension";

// Build the DBCONSTRAINTDESC structure needed to make the 
// ITableDefinitionWithConstraints::AddConstraint 
// call to add the constraint. 
rgConstraintDescs[0].pConstraintID = &ConstraintName;
rgConstraintDescs[0].ConstraintType = DBCONSTRAINTTYPE_UNIQUE;
rgConstraintDescs[0].cColumns = 1;
rgConstraintDescs[0].rgColumnList = ColumnList;
rgConstraintDescs[0].Deferrability = 0;  // Constraints are not deferrable.
// The following properties are not used in UNIQUE constraints
rgConstraintDescs[0].pReferencedTableID = NULL;
rgConstraintDescs[0].cForeignKeyColumns = 0;
rgConstraintDescs[0].rgForeignKeyColumnList = NULL;
rgConstraintDescs[0].pwszConstraintText = NULL;
rgConstraintDescs[0].UpdateRule = DBUPDELRULE_NOACTION;
rgConstraintDescs[0].DeleteRule = DBUPDELRULE_NOACTION;
rgConstraintDescs[0].MatchType = DBMATCHTYPE_NONE;

// Add the new constraint
hr = pITbleDefWithConstrt->AddConstraint(&TableName, rgConstraintDescs);

Exit: 

// When finished, clear the properties arrays and release interfaces.
// Uninitialize the environment.

Return;