Restrições do OLE DB (SQL Server Compact)
O Microsoft SQL Server Compact 4.0 dá suporte a restrições para impor as regras de integridade de dados em um banco de dados.
Usando restrições
As restrições são adicionadas a colunas através da interface ITableDefinitionWithConstraints. Para restrições de chave estrangeira, o elemento matchType da estrutura DBCONSTRAINTDESC deve sempre ser definido como DBMATCHTYPE_FULL.
Dica
O mecanismo de banco de dados do SQL Server Compact 4.0 impõe algumas limitações nos tipos de restrição e na forma como eles funcionam. Para obter mais informações, consulte Compreendendo os bancos de dados (SQL Server Compact).
Exemplos
O exemplo a seguir mostra como usar o OLE DB para adicionar uma nova restrição UNIQUE à coluna Extension (Extensão) da tabela Employees (Funcionários) com o 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;