I'm unsure what you see as a problem here? The only conflict with relational
theory would occur if MySQL *permitted* NULL values in primary keys. According
to the error message you received, MySQL is doing a fine job of preventing this.
CREATE TABLE test (
col1 INT PRIMARY KEY,
col2 INT
);
As a test, I created a table (in MySQL 4.0.12) using the above code. MySQL
successfully created the table and set 'col1' to NOT NULL. The problems arise,
and I assume this is what you attempted, when creating a table as below:
CREATE TABLE test (
col1 INT,
col2 INT,
PRIMARY KEY (col1)
);
When attempting to create the table in that manner, I received Error 1171, too.
Still, I'm hard pressed to state that this is not in accordance with SQL92
standards because I was unable to find in the standards document where it states
that the column must automatically be declared NOT NULL. However, given the
following text from the MySQL Manual, I will concur that there is a problem at
hand.
from http://www.mysql.com/doc/en/CREATE_TABLE.html:
a.. A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT
NULL. If they are not explicitly declared as NOT NULL, it will be done
implicitly (and quietly). In MySQL the key is named PRIMARY. A table can have
only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask
for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key,
which doesn't have any NULL columns, as the PRIMARY KEY.
a.. A PRIMARY KEY can be a multiple-column index. However, you cannot create a
multiple-column index using the PRIMARY KEY key attibute in a column
specification. Doing so will mark only that single column as primary. You must
use the PRIMARY KEY(index_col_name, ...) syntax.
Long story short, regardless of whether or not MySQL complies with SQL92 specs,
it currently doesn't even comply with it's own documentation. That, I will
concur, is a problem.
Edward Dudlik
Becoming Digital
www.becomingdigital.com
----- Original Message -----
From: "Neil Zanella" <[EMAIL PROTECTED]>
To: "MySQL Mailing List" <[EMAIL PROTECTED]>
Sent: Wednesday, 04 June, 2003 01:03
Subject: MySQL: standard SQL compliancy: primary keys: default: should be NOT
NULL
Hello,
I believe that MySQL is in error in reporting the following message just
because I did not specify that the PRIMARY KEY should not be NULL. These
days there are standards and SQL92, AFAIK, specifies that if the primary
key is not explicitly set to NOT NULL then the RDBMS should automatically
and silently assume the user means NOT NULL. After all, any half decent
book on relational databases out there will tell you that primary keys
cannot be null. So why does MySQL do it this way. By doing this MySQL is
breaking the portability of my standard SQL code which works so well with
postgreSQL and Oracle 9i. So why donesn't MySQL play nice and abide to
the standard? Is this fixed in MySQL 4? I am running MySQL 3.23.54a as
distributed with Red Hat 9, which, for some reason has not decided to
update their mysql RPMS to MySQL 4 for that release but I am interesting
in knowing if this has been fixed in MySQL. I think one of MySQL's goals
should be to support standards such as SQL92 (if not SQL99). Even if
internally some things don't work as expected, at a minimum, the
parsers should be compatible as much as possible, including
standard data types and assuming primary keys are not null
by default.
ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL
in a key, use UNIQUE instead
BTW, for those willing to check it out, Part 1 (as well as other parts) of
the SQL standard are available from http://webstore.ansi.org/ for 18 bucks
as standard INCITS/ISO/IEC 9075-1-1999. The older standard is also
available as ANSI INCITS 135-1992 (R1998) at the same price.
Regards,
Neil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
