[sqlite] UNIQUE constraint failed: ip_addr.pid
On 2015-09-14 09:04 PM, Petr L?z?ovsk? wrote: > Have following table: > > CREATE TABLE ip_addr > ( > /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL, > /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL, > /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */ > /*! Status: 1 - Allowed, Asigned to concrete customer */ > /*! Status: 2 - Disallowed, Assigned to blocked user */ > /*! Status: 3 - Disallowed, Assigned to history user */ > /*! Status of IP address %%c */ ip_status INTEGER NOT NULL, > /*! Type: 1 - Private */ > /*! Type: 2 - Public */ > /*! Type: 3 - IPv6 */ > /*! Type of IP address %%d */ ip_type INTEGER NOT NULL, > /*! Date of blocking %%e */ blocked_at INTEGER, > /*! Blocking note %%f */ blocking_note VARCHAR > ); > ) > > > If inserting row containing PID already exist in table, sqlite generate > %subj% error. But there is not such constraint in database definition. Did I > miss something? Hi Lazno, I'm guessing you know by now about the uniqueness of a PK, but just in case, here is the link to get the short version direct meaning: https://www.google.com/search?btnG=1=0=Explain+primary+key%3F_rd=ssl Next step, let me suggest how to solve your problem, this is the schema you might need: CREATE TABLE ip_addr ( /* Primary identificator (ID) %%a */ id INTEGER PRIMARY KEY, /* Person identificator (PID) %%a */ pid INTEGER NOT NULL, /* IP address %%b */ ip_addr VARCHAR(16) NOT NULL, /* Status: 0 - Allowed, Unassigned to specific customer (blocked) */ /* Status: 1 - Allowed, Asigned to concrete customer */ /* Status: 2 - Disallowed, Assigned to blocked user */ /* Status: 3 - Disallowed, Assigned to history user */ /* Status of IP address %%c */ ip_status INTEGER NOT NULL, /* Type: 1 - Private */ /* Type: 2 - Public */ /* Type: 3 - IPv6 */ /* Type of IP address %%d */ ip_typeINTEGER NOT NULL, /* Date of blocking %%e*/ blocked_at INTEGER, /* Blocking note %%f */ blocking_note TEXT ); CREATE INDEX pid_idx ON ip_addr(pid); /* Non-Unique Index for pid allowing Indexing with multiple of the same ip's */ Notes: When you declare any column as INTEGER PRIMARY KEY, it becomes an alias for the row_id, which means that: - A: It must be Unique, - B: It cannot contain NULLs by design (Other primary keys may contain NULLs, but not the row_id), so no need to add NOT NULL, and - C: If you do not intend to access the primary key ever (say you will only ever look at the pid Index here), then you can just leave out this line: /* Primary identificator (ID) %%a */ id INTEGER PRIMARY KEY, Also, note that SQLite can do VARCHAR(16) and while the 16 is useful (for query optimization for instance), the column won't ever be constrained to 16 chars, you can easily put 200 chars in there and it will keep the full line. Lastly, in the last line where VARCHAR appeared without a range specifier, you could use just TEXT (as in my example) which is the native SQLite type (or affinity) for string-type data. If you will use look-ups by any of the text fields, I suggest Indexing them and also add COLLATE NOCASE in the declaration to avoid case sensitivity. Cheers, Ryan
[sqlite] UNIQUE constraint failed: ip_addr.pid
Thay track all of us and "optimalise" search results. My list of links could differ from yours. I have googled key phrase '"UNIQUE constraint failed:" sqlite primary key' and similar, but not read whole received documents, perform only brief look on it. This is best practice by my experience, english is not my first language and reading whole long documents is painfullness for me. Apology to boring experts with dumb question. L. > Hi Petr, > if you Google for "database table primary key" the first few results lead to > quite good explanations. > Also, the english wikipedia's article "Unique key" explains primary keys. > HTH > Martin > Am 14.09.2015 22:25 schrieb Petr L?z?ovsk? : >> I had googled to verify such idea before, but have no luck. >> Thanks, L. >> > Hello Petr, >> > defining the column pid as INTEGER PRIMARY KEY you added an implicit >> > contraint; a primary key means that only one record with a given value >> > of pid can exist in the table. >> > See https://www.sqlite.org/lang_createtable.html#rowid >> > Martin >> > Am 14.09.2015 um 21:04 schrieb Petr L?z?ovsk?: >> >> Have following table: >> >> CREATE TABLE ip_addr >> >> ( >> >> /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL, >> >> /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL, >> >> /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */ >> >> /*! Status: 1 - Allowed, Asigned to concrete customer */ >> >> /*! Status: 2 - Disallowed, Assigned to blocked user */ >> >> /*! Status: 3 - Disallowed, Assigned to history user */ >> >> /*! Status of IP address %%c */ ip_status INTEGER NOT NULL, >> >> /*! Type: 1 - Private */ >> >> /*! Type: 2 - Public */ >> >> /*! Type: 3 - IPv6 */ >> >> /*! Type of IP address %%d */ ip_type INTEGER NOT NULL, >> >> /*! Date of blocking %%e */ blocked_at INTEGER, >> >> /*! Blocking note %%f */ blocking_note VARCHAR >> >> ); >> >> ) >> >> If inserting row containing PID already exist in table, sqlite generate >> >> %subj% error. But there is not such constraint in database definition. >> >> Did I miss something? >> >> L. >> >> ___ >> >> sqlite-users mailing list >> >> sqlite-users at mailinglists.sqlite.org >> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ >> > sqlite-users mailing list >> > sqlite-users at mailinglists.sqlite.org >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UNIQUE constraint failed: ip_addr.pid
Hi Petr, if you Google for "database table primary key" the first few results lead to quite good explanations. Also, the english wikipedia's article "Unique key" explains primary keys. HTH Martin Am 14.09.2015 22:25 schrieb Petr L?z?ovsk? : > > I had googled to verify such idea before, but have no luck. > > Thanks, L. > > > Hello Petr, > > > defining the column pid as INTEGER PRIMARY KEY you added an implicit > > contraint; a primary key means that only one record with a given value > > of pid can exist in the table. > > See https://www.sqlite.org/lang_createtable.html#rowid > > > Martin > > > Am 14.09.2015 um 21:04 schrieb Petr L?z?ovsk?: > >> Have following table: > > >> CREATE TABLE ip_addr > >> ( > >> /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL, > >> /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL, > >> /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */ > >> /*! Status: 1 - Allowed, Asigned to concrete customer */ > >> /*! Status: 2 - Disallowed, Assigned to blocked user */ > >> /*! Status: 3 - Disallowed, Assigned to history user */ > >> /*! Status of IP address %%c */ ip_status INTEGER NOT NULL, > >> /*! Type: 1 - Private */ > >> /*! Type: 2 - Public */ > >> /*! Type: 3 - IPv6 */ > >> /*! Type of IP address %%d */ ip_type INTEGER NOT NULL, > >> /*! Date of blocking %%e */ blocked_at INTEGER, > >> /*! Blocking note %%f */ blocking_note VARCHAR > >> ); > >> ) > > >> If inserting row containing PID already exist in table, sqlite generate > >> %subj% error. But there is not such constraint in database definition. Did > >> I miss something? > > >> L. > > >> ___ > >> sqlite-users mailing list > >> sqlite-users at mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UNIQUE constraint failed: ip_addr.pid
I had googled to verify such idea before, but have no luck. Thanks, L. > Hello Petr, > defining the column pid as INTEGER PRIMARY KEY you added an implicit > contraint; a primary key means that only one record with a given value > of pid can exist in the table. > See https://www.sqlite.org/lang_createtable.html#rowid > Martin > Am 14.09.2015 um 21:04 schrieb Petr L?z?ovsk?: >> Have following table: >> CREATE TABLE ip_addr >> ( >> /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL, >> /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL, >> /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */ >> /*! Status: 1 - Allowed, Asigned to concrete customer */ >> /*! Status: 2 - Disallowed, Assigned to blocked user */ >> /*! Status: 3 - Disallowed, Assigned to history user */ >> /*! Status of IP address %%c */ ip_status INTEGER NOT NULL, >> /*! Type: 1 - Private */ >> /*! Type: 2 - Public */ >> /*! Type: 3 - IPv6 */ >> /*! Type of IP address %%d */ ip_type INTEGER NOT NULL, >> /*! Date of blocking %%e */ blocked_at INTEGER, >> /*! Blocking note %%f */ blocking_note VARCHAR >> ); >> ) >> If inserting row containing PID already exist in table, sqlite generate >> %subj% error. But there is not such constraint in database definition. Did I >> miss something? >> L. >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UNIQUE constraint failed: ip_addr.pid
Hello Petr, defining the column pid as INTEGER PRIMARY KEY you added an implicit contraint; a primary key means that only one record with a given value of pid can exist in the table. See https://www.sqlite.org/lang_createtable.html#rowid Martin Am 14.09.2015 um 21:04 schrieb Petr L?z?ovsk?: > Have following table: > > CREATE TABLE ip_addr > ( > /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL, > /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL, > /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */ > /*! Status: 1 - Allowed, Asigned to concrete customer */ > /*! Status: 2 - Disallowed, Assigned to blocked user */ > /*! Status: 3 - Disallowed, Assigned to history user */ > /*! Status of IP address %%c */ ip_status INTEGER NOT NULL, > /*! Type: 1 - Private */ > /*! Type: 2 - Public */ > /*! Type: 3 - IPv6 */ > /*! Type of IP address %%d */ ip_type INTEGER NOT NULL, > /*! Date of blocking %%e */ blocked_at INTEGER, > /*! Blocking note %%f */ blocking_note VARCHAR > ); > ) > > > If inserting row containing PID already exist in table, sqlite generate > %subj% error. But there is not such constraint in database definition. Did I > miss something? > > L. > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UNIQUE constraint failed: ip_addr.pid
Have following table: CREATE TABLE ip_addr ( /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL, /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL, /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */ /*! Status: 1 - Allowed, Asigned to concrete customer */ /*! Status: 2 - Disallowed, Assigned to blocked user */ /*! Status: 3 - Disallowed, Assigned to history user */ /*! Status of IP address %%c */ ip_status INTEGER NOT NULL, /*! Type: 1 - Private */ /*! Type: 2 - Public */ /*! Type: 3 - IPv6 */ /*! Type of IP address %%d */ ip_type INTEGER NOT NULL, /*! Date of blocking %%e */ blocked_at INTEGER, /*! Blocking note %%f */ blocking_note VARCHAR ); ) If inserting row containing PID already exist in table, sqlite generate %subj% error. But there is not such constraint in database definition. Did I miss something? L.
