mysql referential integrity

Hello,

can someone tell me why this can work:

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> create table ttest(name varchar(20) not null , lastname varchar(25), primary key(name));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into ttest values('','miller');
Query OK, 1 row affected (0.01 sec)

mysql> select * from ttest;
+------+----------+
| name | lastname |
+------+----------+
| | miller |
+------+----------+
1 row in set (0.01 sec)

MySql inserts a space as the name ... and it's not because of the '' i insert (above)

-----------------------------------------------------------------------------------

And this works too:



mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> create table one (myid int(5) not null auto_increment, myname varchar(10)
, primary key(myid));
Query OK, 0 rows affected (0.01 sec)

mysql> create table two (fmyid int(5), myadr varchar(50) not null, primary key(m
yadr));
Query OK, 0 rows affected (0.01 sec)

mysql> alter table two add constraint foreign key(fmyid) references one(myid);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into two values(10, 'Test for integrity');
Query OK, 1 row affected (0.00 sec)

mysql> select * from one;
Empty set (0.01 sec)

------------------------------------------------------------------------------------

What am I doing wrong ???
Where's the referential integrity in mysql ?

Thanks a lot

Sorry...

The question is why can insert an non existent foreign key ???

In the first example your insert query inserts into name then value, yet the name field is empty and the value has the name in it. are you sure you havent mixed up your queries?

as for the second part, you're inserting into a table that has a key setup, but you don't have vals in the first table. MySQL should be reporting an error i'd say if you can add to the second table without a PK in the first... i'm gonna go do some MySQL testing.

In the first example i dont insert the primary key i just insert a value and mysql inserts a space as the key ...
you can try also with:

a,b varchar(20)
a is primary
when the table is empty (or has no primary key with a space)

insert into test (b) values('fnhjdsfnds');
and it will insert a space ...

And for the second example:

Yes that's my problem ... ( i tried with the older and the newer version of mysql ...)

Any ideas ???

you've just listed 'a' as 'primary'. Are you creating a proper primary key as a unique id, like this?

a int auto_increment not null,
...

primary key(a),
unique id(a)
);

???

Yes sure !
create table ( column def, ...., primary key(colname))
I didn't use unique but that shouldn't be the point ...

Why are you not inserting anything into the primary key...you have the following:

create table ttest(name varchar(20) not null , lastname varchar(25), primary key(name));

Then the primary key is name, which cannot be NULL. This always needs to have a value that is diferent for each row. That is why it is a primary key (database theory).

Here is your insert statement:
insert into ttest values('','miller');

MySQL needs to add a value and above you are not suppling a value. So MySQL inserts a space which is not a NULL value because the primary key cannot be NULL.

Your other example works because you have an auto-incrementing field as the primary key. This will always have a value (NOT NULL) and the value will always be diferent (auto-incrementing).

Hope this explains why MySQL added the space. You can also check the docs on this and verify what MySQL does when not suppling a value to a NOT NULL field.

first of all thanks for your reply ...

You said my second example is working 'cause it's an auto-incrementing field, ok, but in database theory a foreign key is a primary key in another table, so it has to exist to do so, but i can insert something with an non-existing key and it works ...

Actually you are right about the foreign key constraint. Mitch also stated the same thing. When I do it I get a referencial integrity violation.

So yes, you can only insert into table two if the foreign key that you are adding is in table one.

What table type are you using with MySQL?

Not that in SQL-92 that foreign key are allowed to be declared as either nonnull or null provided that they have not been declared to be nonnull. Here is the trick part: if ANY of the foreign key columns is NULL, the tuple is defined automatically to satisfy the constraint. This defined definition may not always be the right one so SQL also provides constructs that allow you to change the behavior with NULL values...

Does that make sense...basically, if you have a foreign-key that has on tuple or column defined as null, then the database will define that tuple automatically to satisfy the constraint...

This could possibly be the reason for MySQL allowing you to insert into table two.

I would suggest that you verify MySQL take on foreign-key constraints for NULL values...

PostgreSQL does not allow you to do that seeing that it is basically a referential integrity violation...