Thread: Table with Field Serial - Problem

On 10/31/2013 07:31 AM, Yostin Vargas wrote:
>
> I have a table with only one Field ID type Serial Autonumeric and is a
> PK,  i want insert a new record but it show me error Not null violation,
> but  if i put a value  the first INSERT work correctly but the  next
> Insert it Show me error Unique violation,
>
> So i try adding a new field in this table and put a value null to this
> field, and the ID  Autonumeric  work .
>
> Exist a way to do it only with a field, i'm working with PHP???

Some actual examples form you end would help:)

My guess is you are trying to insert a NULL value into the PK field
instead of just not inserting anything at all. An alternative is to use
the DEFAULT keyword. See below for example.

create table test_table(id_fld serial primary key, char_fld varchar);

test=> \d test_table
                                  Table "public.test_table"
   Column  |       Type        |                          Modifiers

----------+-------------------+-------------------------------------------------------------
  id_fld   | integer           | not null default
nextval('test_table_id_fld_seq'::regclass)
  char_fld | character varying |
Indexes:
     "test_table_pkey" PRIMARY KEY, btree (id_fld)


test=> INSERT INTO test_table (id_fld , char_fld) VALUES (NULL, 't');
ERROR:  null value in column "id_fld" violates not-null constraint

test=> INSERT INTO test_table (char_fld) VALUES ('t');
INSERT 0 1

test=> INSERT INTO test_table (id_fld , char_fld) VALUES (DEFAULT, 't');
INSERT 0 1


>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 10/31/2013 07:55 AM, Yostin Vargas wrote:
>   My table is like  this
>
>   Column  |       Type        |                          Modifiers
> ----------+-------------------__+-----------------------------__------------------------------__--
>   id_fld   | integer           | not null default
> nextval('test_table_id_fld___seq'::regclass)

So you have a single field table, what is the purpose?

>
>
> im using Yii Framework,  How i can Put VALUES (DEFAULT) if i put
> "DEFAULT" is like a string


That would seem to be a question for the Yii mailing list/forum.




--
Adrian Klaver
adrian.klaver@gmail.com

On 10/31/2013 08:23 AM, Yostin Vargas wrote:
> Yes i have a single field because is related to another table that
> contains, the name
>
> it's for to do multilanguage
>
> Example
>
> Table1
>   Column  |       Type        |                          Modifiers
> ----------+-------------------__+-----------------------------__------------------------------__--
>   id   | integer           | not null default
> nextval('test_table_id_fld___seq'::regclass)
>
>
> Table2
> Column  |       Type        |                      related
> ----------+-------------------__+-----------------------------__------------------------------__--
>   id_table1   | integer           |  FK of Table1.id
>   id_lang       | integer          |  FK of lang.id <http://lang.id>
>   name         |  varchar
>

I may be having one of my dumb moments, but what does the above
accomplish that including the serial column in Table2 does not?

>
> I solve it  doin it  " $model->id=new CDbExpression('DEFAULT'); "

Great.

>
> Thanks for your Helps



--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver-3 wrote
>> Table1
>>   Column  |       Type        |                          Modifiers
>> ----------+-------------------__+-----------------------------__------------------------------__--
>>   id   | integer           | not null default
>> nextval('test_table_id_fld___seq'::regclass)
>>
>>
>> Table2
>> Column  |       Type        |                      related
>> ----------+-------------------__+-----------------------------__------------------------------__--
>>   id_table1   | integer           |  FK of Table1.id
>>   id_lang       | integer          |  FK of lang.id
>> <http://lang.id>
>>   name         |  varchar
>>

The PK for table 2 is composite: the serial key from table 1 + the language
id.  The table 1 id has to be able to repeat since the same "entity" needs
multiple translations.  Using a serial on table 2 is also possible but a
separate issue and probably not worth adding since you need a unique index
on (id_table1, id_lang) regardless.

The question is why isn't there some kind of identifier on table 1 that
gives you some idea of what the id/table record is for?

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Table-with-Field-Serial-Problem-tp5776516p5776546.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 10/31/2013 09:32 AM, David Johnston wrote:
> Adrian Klaver-3 wrote
>>> Table1
>>>    Column  |       Type        |                          Modifiers
>>> ----------+-------------------__+-----------------------------__------------------------------__--
>>>    id   | integer           | not null default
>>> nextval('test_table_id_fld___seq'::regclass)
>>>
>>>
>>> Table2
>>> Column  |       Type        |                      related
>>> ----------+-------------------__+-----------------------------__------------------------------__--
>>>    id_table1   | integer           |  FK of Table1.id
>>>    id_lang       | integer          |  FK of lang.id
>>> <http://lang.id>
>>>    name         |  varchar
>>>
>
> The PK for table 2 is composite: the serial key from table 1 + the language
> id.  The table 1 id has to be able to repeat since the same "entity" needs
> multiple translations.  Using a serial on table 2 is also possible but a
> separate issue and probably not worth adding since you need a unique index
> on (id_table1, id_lang) regardless.
>
> The question is why isn't there some kind of identifier on table 1 that
> gives you some idea of what the id/table record is for?

Exactly the id_table1 FK has no context, it is just a number generator,
so why make it separate? If want to just generate numbers why not just
use the sequence directly?

>
> David J.
>
>



--
Adrian Klaver
adrian.klaver@gmail.com

On 10/31/2013 11:12 AM, Yostin Vargas wrote:
> i really dont need a number generator, only a unique PK. but i want that
> this PK be generate automatically
>
> for example i have a Category calling Computer in English but i have the
> same Category in Spanish (Computadora)  i assigned the ID->1 for both

So table1 is the category table:

id serial
category varchar

or

Why not just make your PK a natural one (category, language)?


In the end whatever works for you, works. I am just asking because I
could not follow the logic and I needed guidance.


>
> So if i put the Pk ID  in the table2 number i have error for unique
> violation when i want  INSERT another name in a diferent language for
> the same category
>
> For that reason i declare ID in the table2  like a FK from ID in the table1
>
>



--
Adrian Klaver
adrian.klaver@gmail.com

On Thu, Oct 31, 2013 at 5:13 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>> Table1
>>   Column  |       Type        |                          Modifiers
>>
>> ----------+-------------------__+-----------------------------__------------------------------__--
>>
>>   id   | integer           | not null default
>> nextval('test_table_id_fld___seq'::regclass)
>>
>>
>> Table2
>> Column  |       Type        |                      related
>>
>> ----------+-------------------__+-----------------------------__------------------------------__--
>>
>>   id_table1   | integer           |  FK of Table1.id
>>   id_lang       | integer          |  FK of lang.id <http://lang.id>
>>   name         |  varchar
>>
>
> I may be having one of my dumb moments, but what does the above accomplish
> that including the serial column in Table2 does not?

The default constraint puzzles me a bit, but you can have duplicate
values in table2 and check they are in t1. Imagine something like
this. You store message ids and translations. When a new message is
needed you insert it into t1, put this id wherever it's needed, and
comunicate the id to the translators, which then can insert the
translations in t2 at their pace. It has it uses.

Francisco Olarte.

On 11/02/2013 04:58 AM, Francisco Olarte wrote:
> On Thu, Oct 31, 2013 at 5:13 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>>> Table1
>>>    Column  |       Type        |                          Modifiers
>>>
>>> ----------+-------------------__+-----------------------------__------------------------------__--
>>>
>>>    id   | integer           | not null default
>>> nextval('test_table_id_fld___seq'::regclass)
>>>
>>>
>>> Table2
>>> Column  |       Type        |                      related
>>>
>>> ----------+-------------------__+-----------------------------__------------------------------__--
>>>
>>>    id_table1   | integer           |  FK of Table1.id
>>>    id_lang       | integer          |  FK of lang.id <http://lang.id>
>>>    name         |  varchar
>>>
>>
>> I may be having one of my dumb moments, but what does the above accomplish
>> that including the serial column in Table2 does not?
>
> The default constraint puzzles me a bit, but you can have duplicate
> values in table2 and check they are in t1. Imagine something like
> this. You store message ids and translations. When a new message is
> needed you insert it into t1, put this id wherever it's needed, and
> comunicate the id to the translators, which then can insert the
> translations in t2 at their pace. It has it uses.

I understand the need to generate uniqueness, what I am not
understanding is this method. Table1 is just a series of numbers, so
were is the context that tells you what the numbers mean? To me it boils
down to; if you just want to generate numbers use a sequence directly,
if the numbers have meaning, supply context. Probably have spent too
much time on this already, just one of those things that puzzle:)

>
> Francisco Olarte.
>


--
Adrian Klaver
adrian.klaver@gmail.com