On Mon, Nov 18, 2002 at 09:45:45AM +0100, Martin Holz wrote: > On Sunday 17 November 2002 19:10, Michael Wang wrote: > > The following DDLs on slide "JDBC HOW TO" page for MySQL: > > > > " > > create table objects(uri blob not null, primary key uriIndex (uri(255)), > > classname blob); > > create table revisions(uri blob not null, primary key uriIndex > > (uri(255)), isversioned int, initialrevision varchar(10) ); > > " > > > > For this reason, and the reason I mentioned above, the "primary key" > > should be replaced by "key" in the DDL statements. > > What is the difference between 'key' and 'primary key' except, that a 'key' > might be null? Both must be unique. So I would expect the same problems. Martin et al,
There are three types of indexes (keys) in MySQL (plus fulltext), and they are summarized below (same as Oracle): regular key: allows null=YES, allows duplicates=YES unique key: allows null=YES, allows duplicates=NO (*) primary key: allows null=NO , allows duplicates=NO * The key allows NULLs, but you can have "not null" in column definition. And this is demonstrated with examples below: key: mysql> create table a (b int); Query OK, 0 rows affected (0.00 sec) mysql> alter table a add index ind_a (b); Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into a values (null); Query OK, 1 row affected (0.01 sec) mysql> insert into a values (1); Query OK, 1 row affected (0.02 sec) mysql> insert into a values (1); Query OK, 1 row affected (0.01 sec) unique key: mysql> create table a (b int); Query OK, 0 rows affected (0.01 sec) mysql> alter table a add unique ind_a (b); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into a values (null); Query OK, 1 row affected (0.03 sec) mysql> insert into a values (1); Query OK, 1 row affected (0.01 sec) mysql> insert into a values (1); ERROR 1062: Duplicate entry '1' for key 1 primary key: mysql> create table a (b int); Query OK, 0 rows affected (0.01 sec) mysql> alter table a add primary key (b); ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead mysql> alter table a modify b int not null; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table a add primary key (b); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into a values (null); ERROR 1048: Column 'b' cannot be null mysql> insert into a values (1); Query OK, 1 row affected (0.00 sec) mysql> insert into a values (1); ERROR 1062: Duplicate entry '1' for key 1 -- Michael Wang http://www.unixlabplus.com/ -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
