Fillschema does not import constraints


Danilo P.

Hi everyone.

I'm trying to interact with ADO.NET and its DataAdapter and DataSet classes
to discover the potential this tool offers.

After studied all concern the theory I start to play with compiler and in
one of my code snippet I try to fill the schema of a SQL Server table in a
DataTable object with FillSchema method.

The table's definition is this:

CREATE TABLE [dbo].[Employs] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[Name] [varchar] (20) NULL ,

[Surname] [varchar] (20) NULL


This table has no primary key but it has two unique distinct constraints,
one for field.

The lack of a primary key is only for show what happen.

ALTER TABLE [dbo].[Employs] ADD









Then, I create a DataAdapter object who interact with such table.

Dim dsData As New DataSet

Dim cn As New SqlConnection("Data Source=(local);...etc...")

Dim da As New SqlDataAdapter("SELECT * FROM Employs ", cn)

da.FillSchema(dsData, SchemaType.Source, " Employs ")

Once executed the FillSchema method I show the Table object constraints:

debug.WriteLine(dsData.Tables(0).Constraints.Count) --> 1

debug.WriteLine(dsData.Tables(0).Columns("Name").Unique) --> True

debug.WriteLine(dsData.Tables(0).Columns("Surname").Unique) --> False

This behaviour is the same also if I precede the FillSchema method with an
assignment like this:

da.MissingSchemaAction = MissingSchemaAction.AddWithKey

The matter is: why the second constraints is not correctly imported in the
DataTable definition?

Fillschema method works only with one constraints, perhaps?

Thank you in advance.


Danilo P.


If I add a primary key in the ID field, FillSchema will import correctly
this key but not the follow two.

David Sceppa


This behavior is intentional.

DataAdapter.FillSchema translates to Command.ExecuteReader with a
combined CommandBehavior of SchemaOnly + KeyInfo. The Command asks the
back end for some additional schema information including base table and
column names and key information to uniquely identify the rows in the
resultset. It does not return all schema - additional unique constraints,
check constraints, defaults, etc.

I hope this information proves helpful.

David Sceppa
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question