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&pws=0&q=Explain+primary+key%3F&gws_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_type INTEGER 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
