OLE DB 제약 조건(SQL Server Compact)
Microsoft SQL Server Compact 4.0은 데이터베이스에서 데이터 무결성 규칙을 적용하는 제약 조건을 지원합니다.
제약 조건 사용
제약 조건은 ITableDefinitionWithConstraints 인터페이스를 사용하여 열에 추가합니다. FOREIGN KEY 제약 조건에서 DBCONSTRAINTDESC 구조의 matchType 요소는 항상 DBMATCHTYPE_FULL로 설정해야 합니다.
참고
SQL Server Compact 4.0 데이터베이스 엔진에서는 제약 조건 유형 및 제약 조건의 작동 방법을 일부 제한합니다. 자세한 내용은 데이터베이스 이해(SQL Server Compact)를 참조하십시오.
예
다음 예에서는 OLE DB의 ITableDefinitionWithConstraints::AddConstraint 메서드를 사용하여 새 UNIQUE 제약 조건을 Employees 테이블의 Extension 열에 추가하는 방법을 보여 줍니다.
// 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;