CREATE TABLE default data question

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-at-attglobal.net
==================

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-at-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.
>


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?

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.

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