Restricciones OLE DB (SQL Server Compact)

Microsoft SQL Server Compact 3.5 (SQL Server Compact 3.5) admite restricciones para exigir el cumplimiento de las reglas de integridad de datos en una base de datos.

Usar restricciones

Las restricciones se agregan a las columnas con la interfaz ITableDefinitionWithConstraints. En el caso de las restricciones de clave externa, el elemento matchType de la estructura DBCONSTRAINTDESC debe establecerse siempre en DBMATCHTYPE_FULL.

Nota

El motor de base de datos de SQL Server Compact 3.5 establece varias limitaciones en los tipos de restricciones y el modo en que funcionan. Para obtener más información, vea Descripción de las bases de datos (SQL Server Compact).

Ejemplos

El ejemplo siguiente muestra cómo se puede utilizar OLE DB para agregar una nueva restricción UNIQUE a la columna Extension de la tabla Employees utilizando el método ITableDefinitionWithConstraints::AddConstraint.

// 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;