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

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
On 25 Nov 2019, at 11:02am, Jaroslav Homisin <[hidden email]> 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           XID1        0           BINARY      1        
1           2           XID2        0           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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users