Question on MySQL

satimis · 12-07-2008, 05:04 AM

Hi folks,

mysql version
Ver 14.12 Distrib 5.0.32

I ran following command creating a table:-

Code:

mysql> CREATE TABLE `tblPerdition` (
     -> `user` varchar(128),
     -> `servername` varchar(255) default '',
     -> `port` varchar(8) NULL default 'NULL', 
    -> PRIMARY KEY (`user`) 
    -> ); 
Query OK, 0 rows affected (0.00 sec)

Code:

mysql> DESCRIBE tblPerdition;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| user       | varchar(128) | NO   | PRI |         |       |
| servername | varchar(255) | YES  |     |         |       |
| port       | varchar(8)   | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

I can't figure out how to made Null columns of 'user' blank (without NO)
and 'servername' blank (without YES)

Please help. TIA

B.R.
satimis

niknah · 12-07-2008, 06:34 AM

You can't make the primary key null cause then only one row would be allowed to be null(primary keys are unique)

Try this...

CREATE TABLE `tblPerdition` (
`user` varchar(128),
`servername` varchar(255) not null,
`port` varchar(8) NULL default 'NULL',
);

satimis · 12-07-2008, 06:49 AM

Quote:

Originally Posted by niknah

You can't make the primary key null cause then only one row would be allowed to be null(primary keys are unique)

Try this...

CREATE TABLE `tblPerdition` (
`user` varchar(128),
`servername` varchar(255) not null,
`port` varchar(8) NULL default 'NULL',
);

Hi niknah,

Thanks for your advice.

Tried unable to work for me;

Code:

mysql> CREATE TABLE `tblPerdition` (
     -> `user` varchar(128),
     -> `servername` varchar(255) not null,
     -> `port` varchar(8) NULL default 'NULL',
     -> PRIMARY KEY (`user`)
     -> );
Query OK, 0 rows affected (0.07 sec)

Code:

mysql> DESCRIBE tblPerdition;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| user       | varchar(128) | NO   | PRI |         |       |
| servername | varchar(255) | NO   |     |         |       |
| port       | varchar(8)   | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

If putting on the 3rd line;

Code:

`servername` varchar(255) null '' default '',

It complained syntax incorrect.

Edit:

On Null field does it only have "NOT NULL" and "NULL"?

I'm following MySQL on following thread;
http://www.vergenet.net/linux/perdit...tiondb.5.shtml

to setup this table.

B.R.
satimis

niknah · 12-07-2008, 05:52 PM

Quote:

If putting on the 3rd line;

Code:

`servername` varchar(255) null '' default '',

It complained syntax incorrect.

That was your line that you had put there?
I had removed the primary key too.

To make the perdition table use...

CREATE TABLE `tblPerdition` (
`user` varchar(128) primary key,
`servername` varchar(255) not null,
`port` varchar(8)
);

satimis · 12-07-2008, 06:49 PM

Quote:

Originally Posted by niknah

That was your line that you had put there?
I had removed the primary key too.

To make the perdition table use...

CREATE TABLE `tblPerdition` (
`user` varchar(128) primary key,
`servername` varchar(255) not null,
`port` varchar(8)
);

Hi niknah,

Code:

mysql> CREATE TABLE `tblPerdition` (
    -> `user` varchar(128) primary key,
    -> `servername` varchar(255) not null,
    -> `port` varchar(8)
    -> );
Query OK, 0 rows affected (0.00 sec)

Code:

mysql> DESCRIBE tblPerdition;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| user       | varchar(128) | NO   | PRI |         |       |
| servername | varchar(255) | NO   |     |         |       |
| port       | varchar(8)   | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

I got it. Thanks.

The table on that URL gave me a wrong concept looking for solution to make "Null" blank.

B.R.
satimis