Re: [sqlite] Multiple Unique column missing in System.Data.DataTable.Constraints after Schema Filling

2019-11-25 Thread Simon Slavin 
On 25 Nov 2019, at 11:02am, Jaroslav Homisin  wrote:

> I have expected a list of the two constraints: of Primary key and of Unique. 
> But I can find only Primary Key Constraint.

Where are you getting your list of constraints from ?

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE XRelations (
   ...>   XRID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL , 
   ...> XID1 TEXT (100) NOT NULL , 
   ...> XID2 TEXT (100) NOT NULL , 
   ...> RelationType TEXT (100) NULL , 
   ...> CONSTRAINT unq UNIQUE ( XID1, XID2)
   ...> );
sqlite> INSERT INTO XRelations VALUES (1,'a','n','first');
sqlite> INSERT INTO XRelations VALUES (2,'a','n','first');
Error: UNIQUE constraint failed: XRelations.XID1, XRelations.XID2
sqlite> INSERT INTO XRelations VALUES (1,'b','m','first');
Error: UNIQUE constraint failed: XRelations.XRID
sqlite> .mode column
sqlite> PRAGMA index_list('XRelations');
0   sqlite_autoindex_XRelations_1  1   u   0 
sqlite> PRAGMA index_info('sqlite_autoindex_XRelations_1');
0   1   XID1  
1   2   XID2 
sqlite> PRAGMA index_xinfo('sqlite_autoindex_XRelations_1');
0   1   XID10   BINARY  1 
1   2   XID20   BINARY  1 
2   -1  0   BINARY  0 

This shows details of the index automatically made by SQLite so it can quickly 
find violations of CONSTRAINT unq .
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple Unique column missing in System.Data.DataTable.Constraints after Schema Filling

2019-11-25 Thread Jaroslav Homisin 
Dear SQLite support,

Develop informations:
    Windows 7 64bit
 Visual StudioProfessional 2015 (Version 14.0.25431.01 Update 3);  
   .NET Build Platform 4.5.1
 System.Data.SQLite.dll used from Installation  
sqlite-netFx451-setup-bundle-x64-2013-1.0.112.0.exe
 

I am using followed Table:

CREATE TABLE XRelations (  XRID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL , 
  XID1 TEXT (100) NOT NULL , 
  XID2 TEXT (100) NOT NULL , 
  RelationType TEXT (100) NULL , 
  CONSTRAINT unq UNIQUE ( XID1, 
XID2)
   )

In C# .NET Source Code after filling of Schema this table, I am missing the 
Unique constraint. I have expected a list of the two constraints: of Primary 
key and of Unique. But I can find only Primary Key Constraint.
Note: the Unique Constraint works on the Database fine, if I try to insert 
redundant row, occurs Exception: UNIQUE constraint failed. 

DataTable pDt = new DataTable(ptTabellenName);
string ltSQL = "select * from " + ptTabellenName + " where 1=0";
System.Data.SQLite.SQLiteDataAdapter lSQLiteDataAdapterSchema = new 
System.Data.SQLite.SQLiteDataAdapter();
lSQLiteDataAdapterSchema.FillSchema(pDt, SchemaType.Mapped);

 

I have tried some other syntax, but withou effect:
CREATE TABLE XRelations (  XRID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL , 
  XID1 TEXT (100) NOT NULL , 
  XID2 TEXT (100) NOT NULL , 
  RelationType TEXT (100) NULL , 
  UNIQUE ( XID1, XID2)
   )
Or via two SQL commands:
CREATE TABLE XRelations (  XRID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL , 
  XID1 TEXT (100) NOT NULL , 
  XID2 TEXT (100) NOT NULL , 
  RelationType TEXT (100) NULL 
   );
CREATE UNIQUE INDEX unq ON XRelations (XID1, XID2);


/


If think the Problem is only by multiple column for Unique Key. If I reduce the 
Unique constrain for single column, then it works:

CREATE TABLE XRelations (  XRID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL , 
  XID1 TEXT (100) NOT NULL , 
  XID2 TEXT (100) NOT NULL , 
  RelationType TEXT (100) NULL , 
  CONSTRAINT unq UNIQUE (XID2)
   )

 


I can find in DataTable.Constrints also Constraint for multiple Primary Key 
column, but never Constraint for multiple Unique column.

  Do you can help me or explain me why it doesn't work?

  Regards,
 Jaroslav Homisin 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users