Menu
I use postgresql and yii2 framework. Well I got a very interesting error message:
SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, 1, null, null, null, null, 1, Demo, , , , 1998-01-01, , , , 345345435453453, , , , , 1, , , f, f, f, f, 10, f, 1, f, f, f, null, null, null, 1470477479, 1470477479, null). But I checked my Insert command, and there is not "id" column there!
INSERT INTO "advertiser" ("languages", "type", "name", "display_name", "title", "about", "birthday", "gender", "country_id", "county_id", "city_id", "city_part", "street", "house_number", "phone", "public_email", "public_url", "motto", "message", "im_facebook", "im_skype", "has_viber", "has_whatsapp", "has_sms_response", "visible_birthday", "is_checked", "status", "version", "user_id", "created_at", "updated_at") VALUES (NULL, 1, 'Demo', '', '', '', '1998-01-01', 1, NULL, NULL, NULL, '', '', '', '345345435453453', '', '', '', '', '', '', FALSE, FALSE, FALSE, FALSE, FALSE, 10, NULL, 1, 1470477479, 1470477479) RETURNING "id" So I really cannot understand this error message. I do not find that the Postgres or Yii try to insert a null ID value or what.
By the way here you can find the structure
Table "public.advertiser" Column | Type | Modifiers | Storage | Stats target | Description -----------------------+------------------------+---------------------------------+----------+--------------+------------- id | integer | not null | plain | | user_id | integer | | plain | | country_id | integer | | plain | | county_id | integer | | plain | | city_id | integer | | plain | | district_id | integer | | plain | | type | smallint | | plain | | name | character varying(255) | not null | extended | | display_name | character varying(255) | default NULL::character varying | extended | | title | character varying(255) | default NULL::character varying | extended | | about | text | | extended | | birthday | date | not null | plain | | city_part | character varying(255) | default NULL::character varying | extended | | street | character varying(255) | default NULL::character varying | extended | | house_number | character varying(20) | default NULL::character varying | extended | | phone | character varying(15) | not null | extended | | public_email | character varying(255) | default NULL::character varying | extended | | public_url | character varying(255) | default NULL::character varying | extended | | motto | character varying(255) | default NULL::character varying | extended | | message | text | | extended | | gender | smallint | not null default 1 | plain | | im_facebook | character varying(255) | default NULL::character varying | extended | | im_skype | character varying(255) | default NULL::character varying | extended | | has_viber | boolean | not null default false | plain | | has_whatsapp | boolean | not null default false | plain | | has_sms_response | boolean | not null default false | plain | | visible_birthday | boolean | not null default false | plain | | status | smallint | not null default 10 | plain | | is_checked | boolean | not null default false | plain | | geo_latitude | double precision | | plain | | geo_longitude | double precision | | plain | | languages | integer[] | | extended | | created_at | integer | | plain | | updated_at | integer | | plain | | version | bigint | default 0 | plain | | Indexes: "advertiser_pkey" PRIMARY KEY, btree (id) What is your advice? Where should I looking for the problem?
351
To add a not-null constraint, which cannot be written as a table constraint, use this syntax: ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; The constraint will be checked immediately, so the table data must satisfy the constraint before it can be added.
A foreign key containing null values cannot match the values of a parent key, since a parent key by definition can have no null values. However, a null foreign key value is always valid, regardless of the value of any of its non-null parts.
An integer column can be null, but '' is an empty string not null. The right syntax for a null integer (or any other sql type) is null .
Example - With SELECT StatementSELECT * FROM employees WHERE first_number IS NULL; This PostgreSQL IS NULL example will return all records from the employees table where the first_name contains a NULL value.
You aren't inserting a value for id. Since you don't explicitly set it, it's implicitly given a null value, which is, of course, not a valid value for a primary key column. You can avoid this entire situation by defining this column as serial instead of a plain old integer, and leave all the heavy lifting to the database.
154
The serial keyword is expanded at parse time and cannot be seen afterward.
From the version Postgresql 10 there is the following alternative:
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY It is supposed to conform to the SQL standard and thus be compatible with Oracle.
See this blog for more details.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
