Обсуждение: primary key and insert

Поиск
Список
Период
Сортировка

primary key and insert

От
Marc Fromm
Дата:
I created this table:
   Column   |          Type                   | Modifiers
    ------------+------------------------+------------------------
   first_name | character varying(20)  |
  last_name  | character varying(30)  |
 w_number   | character varying(9)    | 
      alert      | character varying(200) | 
  post_date  | date                           |
       id         | integer                       | not null
Indexes:
    "alert_list_pkey" PRIMARY KEY, btree (id)
I get this error when I run the insert a listed below. The insert does not have an entry for the primary key "id" since I thought it updates automatically:
Warning: pg_query() [function.pg-query]: Query failed: ERROR: null value in column "id" violates not-null constraint
 
$sql = "INSERT INTO alert_list (
                      w_number,
                      first_name,
                      last_name,
                      alert,
                      post_date
                  )
                  VALUES (
                      '$txtStudentNumber',
                      '$txtStudentFirstName',
                      '$txtStudentLastName',
                      '$txtStudentAlert',
                      'now()'
                  )";
How do I do an insert on this table and have the primary key "id" update with the record?
 
thanks
 
Marc

Re: primary key and insert

От
Steve Crawford
Дата:
Marc Fromm wrote:
> I created this table:
> ...
>        id         | integer                       | not null
> Indexes:
>     "alert_list_pkey" PRIMARY KEY, btree (id)
> I get this error when I run the insert a listed below. The insert does
> not have an entry for the primary key "id" since I thought it updates
> automatically:
> *Warning*: pg_query() [function.pg-query
> <http://finaid46.finaid.wwu.edu/lan/student_alerts/function.pg-query>]:
> Query failed: ERROR: null value in column "id" violates not-null
> constraint
> ...
> How do I do an insert on this table and have the primary key "id"
> update with the record?
>
I think you are confusing primary key and serial.

A column can be a primary key (not-null, unique row id) or a serial
(actually automatically creates an integer type, a sequence, and a
default for the column of nextval('sequence') to automatically generate
a new value for each record).

A primary key could be a serial, but doesn't have to be.

A serial can be a primary key but doesn't have to be.

Note: due to things like transaction rollbacks, a serial column will
have unique IDs generated but they are not guaranteed to be consecutive.

Cheers,
Steve


Re: primary key and insert

От
johnf
Дата:
On Thursday 11 December 2008 03:27:59 pm Marc Fromm wrote:
>  id         | integer                       | not null

I believe you did not set the field 'id' to a data type of serial.  If you had
you would have "nextval(" in the Modifiers column.

--
John Fabiani

Re: primary key and insert

От
Suresh Borse
Дата:
Create a sequence and use the sequence in your insert statement.


On Thu, 2008-12-11 at 15:27 -0800, Marc Fromm wrote:
I created this table:
   Column   |          Type                   | Modifiers
    ------------+------------------------+------------------------
   first_name | character varying(20)  |
  last_name  | character varying(30)  |
 w_number   | character varying(9)    | 
      alert      | character varying(200) | 
  post_date  | date                           |
       id         | integer                       | not null
Indexes:
    "alert_list_pkey" PRIMARY KEY, btree (id)

I get this error when I run the insert a listed below. The insert does not have an entry for the primary key "id" since I thought it updates automatically:
Warning: pg_query() [function.pg-query]: Query failed: ERROR: null value in column "id" violates not-null constraint
 
$sql = "INSERT INTO alert_list (
                      w_number,
                      first_name,
                      last_name,
                      alert,
                      post_date
                  )
                  VALUES (
                      '$txtStudentNumber',
                      '$txtStudentFirstName',
                      '$txtStudentLastName',
                      '$txtStudentAlert',
                      'now()'
                  )";

How do I do an insert on this table and have the primary key "id" update with the record?
 
thanks
 
Marc
Thanks & Regards,

Suresh Borse
(DBA)
_________________________________________________________________________________________
DIRECTION SOFTWARE SOLUTIONS
5, Brady Gladys Plaza, 1/447, Senapati Bapat Marg, Lower Parel, Mumbai - 400 013
Tel.   : 91 22 66615000     (Ext: 350)             Fax    : 91 22 24911046             
Cell   : 91 9422 239338                               Site    : www.direction.biz
_________________________________________________________________________________________