When you create a table, what's the syntax for setting default
values.

For example (pseudo):
CREATE TABLE mytable (
id INT PRIMARY KEY AUTO_INCREMENT,
myoption DEFAULT ['option1', 'option2']
)

Sanders Kaufman wrote:
> When you create a table, what's the syntax for setting default values.
>
> For example (pseudo):
> CREATE TABLE mytable (
> id INT PRIMARY KEY AUTO_INCREMENT,
> myoption DEFAULT ['option1', 'option2']
> )

I'm not sure what you're trying to do. What is the type of "myoption"?
And a column can have only one default value.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> Sanders Kaufman wrote:
>
>> When you create a table, what's the syntax for setting default values.
>>
>> For example (pseudo):
>> CREATE TABLE mytable (
>> id INT PRIMARY KEY AUTO_INCREMENT,
>> myoption DEFAULT ['option1', 'option2']
>> )
>
>
> I'm not sure what you're trying to do. What is the type of "myoption"?
> And a column can have only one default value.
>


A DEFAULT value can only be *one* value (not a list of values). If you want to
make sure only certain values are in a field, then you want an "enumerated"
field or a constraint. See the docs for each method.

--
Michael Austin.
Database Consultant

Jerry Stuckle wrote:
> Sanders Kaufman wrote:
>> When you create a table, what's the syntax for setting default values.
>>
>> For example (pseudo):
>> CREATE TABLE mytable (
>> id INT PRIMARY KEY AUTO_INCREMENT,
>> myoption DEFAULT ['option1', 'option2']
>> )
>
> I'm not sure what you're trying to do. What is the type of "myoption"?
> And a column can have only one default value.

The type doesn't matter.
I just want something (logically) like "DEFAULT IN (a, b, c)".

I'm not sure, but I think some folks call in an ENUM?

Michael Austin wrote:
> Jerry Stuckle wrote:
>> Sanders Kaufman wrote:

>>> When you create a table, what's the syntax for setting default values.
>>> For example (pseudo):
>>> CREATE TABLE mytable (
>>> id INT PRIMARY KEY AUTO_INCREMENT,
>>> myoption DEFAULT ['option1', 'option2']
>>> )

>> I'm not sure what you're trying to do. What is the type of
>> "myoption"? And a column can have only one default value.

> A DEFAULT value can only be *one* value (not a list of values). If you
> want to make sure only certain values are in a field, then you want an
> "enumerated" field or a constraint. See the docs for each method.

Thanks Michael.
I looked up "enumerated" and "constraint" but then drowned in
the results.
Can you narrow it down? I think, without any sense of knowing,
that it has something to do with creating an ENUM.

Sanders Kaufman wrote:

> Jerry Stuckle wrote:
>
>> Sanders Kaufman wrote:
>>
>>> When you create a table, what's the syntax for setting default values.
>>>
>>> For example (pseudo):
>>> CREATE TABLE mytable (
>>> id INT PRIMARY KEY AUTO_INCREMENT,
>>> myoption DEFAULT ['option1', 'option2']
>>> )
>>
>>
>> I'm not sure what you're trying to do. What is the type of
>> "myoption"? And a column can have only one default value.
>
>
> The type doesn't matter.
> I just want something (logically) like "DEFAULT IN (a, b, c)".
>
> I'm not sure, but I think some folks call in an ENUM?


Now we need to narrow down your question just a bit - the answer depends on how
you answer the following:

1) do you want to restrict the values entered into this table?
example:
create table somename ( col1 varchar(20),
col2 enum('0','1','2'));

http://dev.mysql.com/doc/refman/5.0/en/enum.html

2) if no value is specified, do you want it to DEFAULT to some value?
see example below
if yes, what single value do you want as a default?

example:
mysql> create table somename ( col1 varchar(20),
col2 enum('0','1','2') default '0');
Query OK, 0 rows affected (2.19 sec)

***NOTE*** I do not specify col2 and the default value is '0'
mysql> insert into somename (col1) values ('test'),('test1');
Query OK, 2 rows affected (0.14 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from somename;
+-------+------+
| col1 | col2 |
+-------+------+
| test | 0 |
| test1 | 0 |
+-------+------+


--
Michael Austin.
Database Consultant

Michael Austin wrote:
> Sanders Kaufman wrote:

>> I just want something (logically) like "DEFAULT IN (a, b, c)".
>> I'm not sure, but I think some folks call in an ENUM?
>
> Now we need to narrow down your question just a bit - the answer depends
> on how you answer the following:
>
> 1) do you want to restrict the values entered into this table?
> example:
> create table somename ( col1 varchar(20),
> col2 enum('0','1','2'));
>
> http://dev.mysql.com/doc/refman/5.0/en/enum.html


BAM! That's it! I asked the wrong question at the start. I
didn't want "default" value, per se - I just wanted that
constraint in there. That is what that does, right?

So like...

colGender enum('male', 'female') DEFAULT 'male',

would limit the possible values to male and female
, defaulting to male - right?

Sanders Kaufman wrote:

> Michael Austin wrote:
>
>> Sanders Kaufman wrote:
>
>
>>> I just want something (logically) like "DEFAULT IN (a, b, c)".
>>> I'm not sure, but I think some folks call in an ENUM?
>>
>>
>> Now we need to narrow down your question just a bit - the answer
>> depends on how you answer the following:
>>
>> 1) do you want to restrict the values entered into this table?
>> example:
>> create table somename ( col1 varchar(20),
>> col2 enum('0','1','2'));
>>
>> http://dev.mysql.com/doc/refman/5.0/en/enum.html
>
>
>
> BAM! That's it! I asked the wrong question at the start. I didn't
> want "default" value, per se - I just wanted that constraint in there.
> That is what that does, right?
>
> So like...
>
> colGender enum('male', 'female') DEFAULT 'male',
>
> would limit the possible values to male and female
> , defaulting to male - right?
>

Correct.

But for these fields I would use CHAR(1) 'M','F' a little less wasted space.
--
Michael Austin.

In article <FfDnh.28952$hI.19129@newssvr11.news.prodigy.net >,
Michael Austin <maustin@firstdbasource.com> writes:

>>> create table somename ( col1 varchar(20),
>>> col2 enum('0','1','2'));
>>>
>>> http://dev.mysql.com/doc/refman/5.0/en/enum.html
>> BAM! That's it! I asked the wrong question at the start. I didn't
>> want "default" value, per se - I just wanted that constraint in
>> there. That is what that does, right?
>> So like...
>> colGender enum('male', 'female') DEFAULT 'male',
>> would limit the possible values to male and female
>> , defaulting to male - right?

> Correct.

Not quite. It's not a constraint and does not prevent you from
entering 'dunno' into colGender - in this case MySQL silently changes
the entered values to something else.

If you need real constraints, use some other DBMS (e.g. PostgreSQL).

Harald Fuchs wrote:
> Michael Austin <maustin@firstdbasource.com> writes:

>>> So like...
>>> colGender enum('male', 'female') DEFAULT 'male',
>>> would limit the possible values to male and female
>>> , defaulting to male - right?

>> Correct.

> Not quite. It's not a constraint and does not prevent you from
> entering 'dunno' into colGender - in this case MySQL silently changes
> the entered values to something else.

"Silently changes the entered values"?!
I would have thought the insert or update would fail in that case.

Is that true, Michael?