Hi all,


I have create a table A with a foreign key for a relation to table B.
The relation must be benot null so the corresponding attribute is set as
not null.

If I create a new A with the foreign key null, the record is saved with a 0.
I would expect that MySql would send me an exception without saving.
How can I get the correct behaviour ?

Thanks in advance for any .


Pierre

MySQl 4.0.18

On Wed, 19 Dec 2007 14:12:31 +0100, Pierre Gilquin <inconnue@bluewin.ch>
wrote:

> Hi all,
>
>
> I have create a table A with a foreign key for a relation to table B.
> The relation must be benot null so the corresponding attribute is set as
> not null.
>
> If I create a new A with the foreign key null, the record is saved with
> a 0.
> I would expect that MySql would send me an exception without saving.
> How can I get the correct behaviour ?

It does limit the insert here.
Can you show use the CREATE TABLE syntax for both tables? (and does a key
with 0 exist in B, just to be sure?)
--
Rik Wasmus

Thanks Rik,

It's something like that :

CREATE TABLE A( ID BIGINT(9) NOT NULL, linkOnTableBId BIGINT(9) NOT NULL,
.... );

ALTER TABLE interaction ADD PRIMARY KEY (ID);


This is very simple as it is generated by a Object - Relationnal Modelling
tools. The constraint for the relation is not managed in the database.
There is no attempt to create a B with id = 0.

The problem is that the default value for linkOnTableBId is 0 and I cannot
get rid of this.


Pierre






"Rik Wasmus" <luiheidsgoeroe@hotmail.com> a écrit dans le message de news:
op.t3k3ll1z5bnjuv@metallium.lan...
> On Wed, 19 Dec 2007 14:12:31 +0100, Pierre Gilquin <inconnue@bluewin.ch>
> wrote:
>
>> Hi all,
>>
>>
>> I have create a table A with a foreign key for a relation to table B.
>> The relation must be benot null so the corresponding attribute is set as
>> not null.
>>
>> If I create a new A with the foreign key null, the record is saved with
>> a 0.
>> I would expect that MySql would send me an exception without saving.
>> How can I get the correct behaviour ?
>
> It does limit the insert here.
> Can you show use the CREATE TABLE syntax for both tables? (and does a key
> with 0 exist in B, just to be sure?)
> --
> Rik Wasmus

On Wed, 19 Dec 2007 14:38:34 +0100, Pierre Gilquin <inconnue@bluewin.ch>
wrote:
> It's something like that :

'something like' is not what I'm looking for. Could you post the full
definitions? Just run SHOW CREATE TABLE <tablename>. It really saves time
to see the definitions and pinpoint it directly instead of trying to list
all possible reasons why this could/should fail.

> ALTER TABLE interaction ADD PRIMARY KEY (ID);

This is never in a CREATE TABLE syntax.
--
Rik Wasmus

This is the real one :

The main table is Interaction

SHOW CREATE TABLE interaction
CREATE TABLE `interaction` (
`audit_id` mediumint(9) default NULL,
`category` char(3) default NULL,
`ID` bigint(9) NOT NULL default '0',
`impact` char(1) default NULL,
`substance1Id` bigint(9) NOT NULL default '0',
`substance2Id` bigint(9) NOT NULL default '0',
`type` char(1) default NULL,
`pourcent` decimal(5,2) default NULL,
`Ki` decimal(15,5) default NULL,
`Km` decimal(15,5) default NULL,
`heriteAtcFromSubstance` varchar(5) default 'true',
`infoImportId` bigint(9) default NULL,
`impactValue` decimal(15,5) default NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `SUB1_SUB2_IDX`
(`substance1Id`,`substance2Id`,`category`,`type`)
) TYPE=MyISAM

You can see the 2 attributes used for a relation on table Substance

( `substance1Id` bigint(9) NOT NULL default '0',
`substance2Id` bigint(9) NOT NULL default '0',)

if at least one of theses attributes is null, I would like an exception ...

CREATE TABLE `substance` (
`AUC` decimal(15,5) default NULL,
`Cmax` decimal(15,5) default NULL,
`DCI` blob,
`Q0` decimal(15,5) default NULL,
`TDemi` decimal(15,5) default NULL,
`Vd` decimal(15,5) default NULL,
`id` mediumint(9) NOT NULL default '0',
`biodisponibilite` decimal(15,5) default NULL,
`category` char(3) NOT NULL default '',
`idEMediat` mediumint(9) default NULL,
`invisible` tinyint(1) default NULL,
`isomere` char(3) default NULL,
`VERROU` tinyint(1) default NULL,
`metabolite` tinyint(1) default NULL,
`metaboliteActif` tinyint(1) default NULL,
`nomLocal` varchar(255) NOT NULL default '',
`poidsMoleculaire` decimal(15,5) default NULL,
`fu` decimal(15,5) default NULL,
`ka` decimal(15,5) default NULL,
`cl` decimal(15,5) default NULL,
`CmaxSs` decimal(15,5) default NULL,
`tauRefCmaxSs` decimal(15,5) default NULL,
`doseRefCmax` decimal(15,5) default NULL,
`tempsRefAUC` decimal(15,5) default NULL,
PRIMARY KEY (`id`),
KEY `nomSubstance` (`nomLocal`)
) TYPE=MyISAM

Pierre




"Rik Wasmus" <luiheidsgoeroe@hotmail.com> a écrit dans le message de news:
op.t3k4ca0j5bnjuv@metallium.lan...
> On Wed, 19 Dec 2007 14:38:34 +0100, Pierre Gilquin <inconnue@bluewin.ch>
> wrote:
>> It's something like that :
>
> 'something like' is not what I'm looking for. Could you post the full
> definitions? Just run SHOW CREATE TABLE <tablename>. It really saves time
> to see the definitions and pinpoint it directly instead of trying to list
> all possible reasons why this could/should fail.
>
>> ALTER TABLE interaction ADD PRIMARY KEY (ID);
>
> This is never in a CREATE TABLE syntax.
> --
> Rik Wasmus

You are right, I tryed a more simple :

CREATE TABLE A( ID BIGINT(9) NOT NULL, linkOnTableBId BIGINT(9) NOT NULL);

ALTER TABLE A ADD PRIMARY KEY (ID);

and in this case, I cannot save with a null value for linkOnTableBId !
The problem is on the complete definition of my table

Pierre

"Rik Wasmus" <luiheidsgoeroe@hotmail.com> a écrit dans le message de news:
op.t3k4ca0j5bnjuv@metallium.lan...
> On Wed, 19 Dec 2007 14:38:34 +0100, Pierre Gilquin <inconnue@bluewin.ch>
> wrote:
>> It's something like that :
>
> 'something like' is not what I'm looking for. Could you post the full
> definitions? Just run SHOW CREATE TABLE <tablename>. It really saves time
> to see the definitions and pinpoint it directly instead of trying to list
> all possible reasons why this could/should fail.
>
>> ALTER TABLE interaction ADD PRIMARY KEY (ID);
>
> This is never in a CREATE TABLE syntax.
> --
> Rik Wasmus

On 19 Dec, 13:53, "Pierre Gilquin" <incon...@bluewin.ch> wrote:
> This is the real one :
>
> The main table is Interaction
>
> SHOW CREATE TABLE interaction
> CREATE TABLE `interaction` (
> `audit_id` mediumint(9) default NULL,
> `category` char(3) default NULL,
> `ID` bigint(9) NOT NULL default '0',
> `impact` char(1) default NULL,
> `substance1Id` bigint(9) NOT NULL default '0',
> `substance2Id` bigint(9) NOT NULL default '0',
> `type` char(1) default NULL,
> `pourcent` decimal(5,2) default NULL,
> `Ki` decimal(15,5) default NULL,
> `Km` decimal(15,5) default NULL,
> `heriteAtcFromSubstance` varchar(5) default 'true',
> `infoImportId` bigint(9) default NULL,
> `impactValue` decimal(15,5) default NULL,
> PRIMARY KEY (`ID`),
> UNIQUE KEY `SUB1_SUB2_IDX`
> (`substance1Id`,`substance2Id`,`category`,`type`)
> ) TYPE=MyISAM
>
> You can see the 2 attributes used for a relation on table Substance
>
> ( `substance1Id` bigint(9) NOT NULL default '0',
> `substance2Id` bigint(9) NOT NULL default '0',)
>
> if at least one of theses attributes is null, I would like an exception ....
>
> CREATE TABLE `substance` (
> `AUC` decimal(15,5) default NULL,
> `Cmax` decimal(15,5) default NULL,
> `DCI` blob,
> `Q0` decimal(15,5) default NULL,
> `TDemi` decimal(15,5) default NULL,
> `Vd` decimal(15,5) default NULL,
> `id` mediumint(9) NOT NULL default '0',
> `biodisponibilite` decimal(15,5) default NULL,
> `category` char(3) NOT NULL default '',
> `idEMediat` mediumint(9) default NULL,
> `invisible` tinyint(1) default NULL,
> `isomere` char(3) default NULL,
> `VERROU` tinyint(1) default NULL,
> `metabolite` tinyint(1) default NULL,
> `metaboliteActif` tinyint(1) default NULL,
> `nomLocal` varchar(255) NOT NULL default '',
> `poidsMoleculaire` decimal(15,5) default NULL,
> `fu` decimal(15,5) default NULL,
> `ka` decimal(15,5) default NULL,
> `cl` decimal(15,5) default NULL,
> `CmaxSs` decimal(15,5) default NULL,
> `tauRefCmaxSs` decimal(15,5) default NULL,
> `doseRefCmax` decimal(15,5) default NULL,
> `tempsRefAUC` decimal(15,5) default NULL,
> PRIMARY KEY (`id`),
> KEY `nomSubstance` (`nomLocal`)
> ) TYPE=MyISAM
>
> Pierre
>
> "Rik Wasmus" <luiheidsgoe...@hotmail.com> a écrit dans le message de news:
> op.t3k4ca0j5bn...@metallium.lan...
>
> > On Wed, 19 Dec 2007 14:38:34 +0100, Pierre Gilquin <incon...@bluewin.ch>
> > wrote:
> >> It's something like that :
>
> > 'something like' is not what I'm looking for. Could you post the full
> > definitions? Just run SHOW CREATE TABLE <tablename>. It really saves time
> > to see the definitions and pinpoint it directly instead of trying to list
> > all possible reasons why this could/should fail.
>
> >> ALTER TABLE interaction ADD PRIMARY KEY (ID);
>
> > This is never in a CREATE TABLE syntax.
> > --
> > Rik Wasmus

You have defined the fields with a default value of 0. So if the
fields are not supplied for the insert or the supplied values are
NULL, the default will be used.

On Wed, 19 Dec 2007 14:53:35 +0100, Pierre Gilquin <inconnue@bluewin.ch>
wrote:

> This is the real one :
>
> The main table is Interaction
>
> SHOW CREATE TABLE interaction
> CREATE TABLE `interaction` (
> `audit_id` mediumint(9) default NULL,
........
> `impactValue` decimal(15,5) default NULL,
> PRIMARY KEY (`ID`),
> UNIQUE KEY `SUB1_SUB2_IDX`
> (`substance1Id`,`substance2Id`,`category`,`type`)
> ) TYPE=MyISAM
>
> CREATE TABLE `substance` (
> `AUC` decimal(15,5) default NULL,
..........
> `tempsRefAUC` decimal(15,5) default NULL,
> PRIMARY KEY (`id`),
> KEY `nomSubstance` (`nomLocal`)
> ) TYPE=MyISAM

You have no foreign key constraints (what makes you think you do?), and
further more foreign key constraints are not supported in the MyISAM
engine, you'll probably want to convert them to InnoDB.
--
Rik Wasmus

It's seems that it's not the problem. I test with a very simple table

CREATE TABLE `a` (
`ID` bigint(9) NOT NULL default '0',
`linkOnTableBId` bigint(9) NOT NULL default '0',
PRIMARY KEY (`ID`)
) TYPE=MyISAM
then I try to insert :
> insert into a values (1,null)
This give the correct exception : ERROR 1048: Column 'linkOnTableBId' cannot
be null

I still dont know where is the problem is my real table

Thanks

"Captain Paralytic" <paul_lautman@yahoo.com> a écrit dans le message de
news: 646c25a2-8480-47ed-b331-239e86ba62ac...oglegroups.com...
On 19 Dec, 13:53, "Pierre Gilquin" <incon...@bluewin.ch> wrote:


You have defined the fields with a default value of 0. So if the
fields are not supplied for the insert or the supplied values are
NULL, the default will be used.

Yes, there is no foreign key constraints in the database. My
Object-Relationnal mapping take care of this.
So for MySql, it's just a attribute with not null constraint.

But if I insert like this :
insert into interaction (ID) values (99999999)
The interaction is created and substance1Id substance2Id are both 0 !

If I test with a simple test table, I get the correct exception (please read
my reply to Captain Paralityc)

Can the problem be from the unique key ?

Pierre

"Rik Wasmus" <luiheidsgoeroe@hotmail.com> a écrit dans le message de news:
op.t3k5d1155bnjuv@metallium.lan...
On Wed, 19 Dec 2007 14:53:35 +0100, Pierre Gilquin <inconnue@bluewin.ch>
wrote:

You have no foreign key constraints (what makes you think you do?), and
further more foreign key constraints are not supported in the MyISAM
engine, you'll probably want to convert them to InnoDB.
--
Rik Wasmus

> Yes, there is no foreign key constraints in the database. My
> Object-Relationnal mapping take care of this.
> So for MySql, it's just a attribute with not null constraint.

Ugh. You do understand this means you can have invalid
data in your database?

Use constraint on your database, it's the right thing to do.


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

All the constraints are defined in the object model and only the object
framework access the database.
Should not have any problem.

Pierre

"Martijn Tonies" <m.tonies@upscene.removethis.com> a écrit dans le message
de news: 47695aa5$0$2010$e4fe514c@dreader17.news.xs4all.nl. ..
>
>> Yes, there is no foreign key constraints in the database. My
>> Object-Relationnal mapping take care of this.
>> So for MySql, it's just a attribute with not null constraint.
>
> Ugh. You do understand this means you can have invalid
> data in your database?
>
> Use constraint on your database, it's the right thing to do.
>
>
> --
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>

On Wed, 19 Dec 2007 22:09:57 +0100, Pierre Gilquin wrote:
> All the constraints are defined in the object model and only the object
> framework access the database.
> Should not have any problem.

You have a great deal more confidence that the object model will be used
in perpetuity than I do. As I see it, so long as the data is stored in a
RDBMS, eventually someone will want and need to access the data outside
the bounds of your model. And once they are reading your data by other
means (such as running plain SQL against it, or a report-generating tool
of some kind), they will realize that they can also maintain the data
using plain SQL. If you build your authentication into the RDBMS, then
SQL will not be able to bypass your authentication rules. If you build
your constraints into the RDBMS, then it will be harder to bypass those.
One of the big reasons to build stored procedures instead of coding
business rules into applications is than then they are available to the
external RDBMS tools and toys as well, and the business rules and logic
are updated in a single step.

--
95. My dungeon will have its own qualified medical staff complete with
bodyguards. That way if a prisoner becomes sick and his cellmate tells the
guard it's an emergency, the guard will fetch a trauma team instead of
opening up the cell for a look. --Peter Anspach's Evil Overlord List

> All the constraints are defined in the object model and only the object
> framework access the database.
> Should not have any problem.

Should not <> will not.

I repeat:

> > Use constraint on your database, it's the right thing to do.

If you disagree, I suggest you do some reading up on why you're
actually using a (R)DBMS instead of flat files :-)

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

this is probably not the place for this discussion.

But your approach is Database centric. My system is object oriented and the
database is just for object's persistence purpose only.

Pierre

"Martijn Tonies" <m.tonies@upscene.removethis.com> a écrit dans le message
de news: 476a2a76$0$1308$e4fe514c@dreader20.news.xs4all.nl. ..
>
>> All the constraints are defined in the object model and only the object
>> framework access the database.
>> Should not have any problem.
>
> Should not <> will not.
>
> I repeat:
>
>> > Use constraint on your database, it's the right thing to do.
>
> If you disagree, I suggest you do some reading up on why you're
> actually using a (R)DBMS instead of flat files :-)
>
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle
> &
> MS SQL Server
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>

> this is probably not the place for this discussion.
>
> But your approach is Database centric. My system is object oriented and
the
> database is just for object's persistence purpose only.

Wrong.

My approach is aimed at taking proper care of data on the lowest
level possible in your system.

This is nothing but common sense. Read Peter H Coffins post, he
explains it in more detail.

What happens if your object oriented system has faulty logic? You
can get invalid data.

What happens if "something" happens outside your program code?
You can get invalid data.

Solving this at the DBMS level is the best way to go. This, however,
does not mean you shouldn't have decent code in place in your object
oriented system in order to get some meaningfull error messages to
the user and so on, nevertheless, get those checks into place!

It's the right thing to do.

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Martijn ,

I just can agree on one think :
The model of an application must be in only one place but not in a mix of
both. That was the origin of my problem.
Your model is build from tables of a database and that's ok.
My model is a graph of objects and then all fonctionnalities and constaints
must be implemented at that level.
This allows me to change the persistence (from Oracle to MySql or even a
flat files or XML file) without effort.
I change my database from Oracle to mysql and I get this problem because I
forget (or loose it) to set a constraint in the object model.
Then the database was in charge and they have different behavior !
In theory a persistence is not even required but in the real world, I have
to recreate the last graph after a crash or maintenance.
In this model, there is no concept of primary and foreign key as they are
part of the actual persistence system used. It's the job of the ORM
(Object-relationnal mapping) to take care of the underlying persistence
system.
Of course, I am aware that nothing else than the ORM must access directly
the database/files.


It's not that you intend to suggest but you me to have clearer view on
this matter.


Pierre





"Martijn Tonies" <m.tonies@upscene.removethis.com> a écrit dans le message
de news: 476a483c$0$26918$e4fe514c@dreader27.news.xs4all.nl ...
>
>> this is probably not the place for this discussion.
>>
>> But your approach is Database centric. My system is object oriented and
> the
>> database is just for object's persistence purpose only.
>
> Wrong.
>
> My approach is aimed at taking proper care of data on the lowest
> level possible in your system.
>
> This is nothing but common sense. Read Peter H Coffins post, he
> explains it in more detail.
>
> What happens if your object oriented system has faulty logic? You
> can get invalid data.
>
> What happens if "something" happens outside your program code?
> You can get invalid data.
>
> Solving this at the DBMS level is the best way to go. This, however,
> does not mean you shouldn't have decent code in place in your object
> oriented system in order to get some meaningfull error messages to
> the user and so on, nevertheless, get those checks into place!
>
> It's the right thing to do.
>
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle
> &
> MS SQL Server
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>

Hello Pierre,

> I just can agree on one think :
> The model of an application must be in only one place but not in a mix of
> both. That was the origin of my problem.
> Your model is build from tables of a database and that's ok.

This is not my view at all. My programming model has nothing to do
with the fact that I always put constraints in place on the database level.
I've designed systems with an object approach and designed tables
afterwards BUT the tables are normalized so that no data gets duplicated
or becomes invalid due to programming errors. This includes constraints
on the database level separated from your object model.

> My model is a graph of objects and then all fonctionnalities and
constaints
> must be implemented at that level.

Of course, this is perfectly valid and the same as with my projects. Your
constraints and functionality is available in your programming language,
object oriented, PHP scripting, whatever.

> This allows me to change the persistence (from Oracle to MySql or even a
> flat files or XML file) without effort.

This has nothing to do with database constraints!

> I change my database from Oracle to mysql and I get this problem because I
> forget (or loose it) to set a constraint in the object model.

See above, your constraints need to be in place in your object model, this
is correct and makes sense.

> Then the database was in charge and they have different behavior !
> In theory a persistence is not even required but in the real world, I have
> to recreate the last graph after a crash or maintenance.
> In this model, there is no concept of primary and foreign key as they are
> part of the actual persistence system used. It's the job of the ORM
> (Object-relationnal mapping) to take care of the underlying persistence
> system.
> Of course, I am aware that nothing else than the ORM must access directly
> the database/files.

Which may-be beyond your control AND of course, the point about the
failed constraints or bugs still stand.

> It's not that you intend to suggest but you me to have clearer view
on
> this matter.

I agree with your constraints approach in your object model, this is
of course perfectly normal and required for your model to work
properly.

However, the data is the most imporant part of any application or set of
application that actually needs to store data. This is why you should put
constraints on the database level as well. It avoids invalid data despite
programming errors, external/3rd party applications accessing your data,
other programmers working on your object model etc...


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On Thu, 20 Dec 2007 11:48:10 +0100, Martijn Tonies wrote:
> This is nothing but common sense. Read Peter H Coffins post, he
> explains it in more detail.

I do try...

Some day, there'll be a mechanism to grant authorities to stored
procedures instead of users, and life will really get fun. The long
goal of being able to actually enforce things like "only User_X can
delete rows with 'User_X' in column user_id" by granting DELETE to the
procedure and taking it away from all users, forcing deletes through the
SP, will actually be achievable. We can kinda do it with triggers but
it's very messy.

--
15. I will never employ any device with a digital countdown. If I find that
such a device is absolutely unavoidable, I will set it to activate when
the counter reaches 117 and the hero is just putting his plan into action.
--Peter Anspach's list of things to do as an Evil Overlord

> Some day, there'll be a mechanism to grant authorities to stored
> procedures instead of users, and life will really get fun. The long
> goal of being able to actually enforce things like "only User_X can
> delete rows with 'User_X' in column user_id" by granting DELETE to the
> procedure and taking it away from all users, forcing deletes through the
> SP, will actually be achievable. We can kinda do it with triggers but
> it's very messy.

Other DBMSses, like Firebird, already have such grants. And yes, it
allows you to control a lot.


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

ok, I understand better your point of view and I think we are not far for
aggree.

The only difference is about foreign constraint :

I aggree that they can be set in the database as they can be easily ported
(not like trigger and store procedure) to different databases.
With my assumption (data access is allowed only with ORM), the foreign
constraints in the database are just redundant and not useful.
But I have another practical reason :
I tryed a long time ago to set the foreign constraints as well in the
database. But in some circonstance when deleting part of the graph, the
order of the delete generated by my ORM are impredictible and this cause
problem.
Not a good reason and may be already solved but I never trie again.

Pierre


"Martijn Tonies" <m.tonies@upscene.removethis.com> a écrit dans le message
de news: 476a712e$0$4207$e4fe514c@dreader24.news.xs4all.nl. ..
> Hello Pierre,
>
>> I just can agree on one think :
>> The model of an application must be in only one place but not in a mix of
>> both. That was the origin of my problem.
>> Your model is build from tables of a database and that's ok.
>
> This is not my view at all. My programming model has nothing to do
> with the fact that I always put constraints in place on the database
> level.
> I've designed systems with an object approach and designed tables
> afterwards BUT the tables are normalized so that no data gets duplicated
> or becomes invalid due to programming errors. This includes constraints
> on the database level separated from your object model.
>
>> My model is a graph of objects and then all fonctionnalities and
> constaints
>> must be implemented at that level.
>
> Of course, this is perfectly valid and the same as with my projects. Your
> constraints and functionality is available in your programming language,
> object oriented, PHP scripting, whatever.
>
>> This allows me to change the persistence (from Oracle to MySql or even a
>> flat files or XML file) without effort.
>
> This has nothing to do with database constraints!
>
>> I change my database from Oracle to mysql and I get this problem because
>> I
>> forget (or loose it) to set a constraint in the object model.
>
> See above, your constraints need to be in place in your object model, this
> is correct and makes sense.
>
>> Then the database was in charge and they have different behavior !
>> In theory a persistence is not even required but in the real world, I
>> have
>> to recreate the last graph after a crash or maintenance.
>> In this model, there is no concept of primary and foreign key as they are
>> part of the actual persistence system used. It's the job of the ORM
>> (Object-relationnal mapping) to take care of the underlying persistence
>> system.
>> Of course, I am aware that nothing else than the ORM must access directly
>> the database/files.
>
> Which may-be beyond your control AND of course, the point about the
> failed constraints or bugs still stand.
>
>> It's not that you intend to suggest but you me to have clearer view
> on
>> this matter.
>
> I agree with your constraints approach in your object model, this is
> of course perfectly normal and required for your model to work
> properly.
>
> However, the data is the most imporant part of any application or set of
> application that actually needs to store data. This is why you should put
> constraints on the database level as well. It avoids invalid data despite
> programming errors, external/3rd party applications accessing your data,
> other programmers working on your object model etc...
>
>
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle
> &
> MS SQL Server
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
>

> ok, I understand better your point of view and I think we are not far for
> aggree.
>
> The only difference is about foreign constraint :
>
> I aggree that they can be set in the database as they can be easily ported
> (not like trigger and store procedure) to different databases.
> With my assumption (data access is allowed only with ORM), the foreign
> constraints in the database are just redundant and not useful.

Redundant? Your point below shows exactly why they are not redundant.

> But I have another practical reason :
> I tryed a long time ago to set the foreign constraints as well in the
> database. But in some circonstance when deleting part of the graph, the
> order of the delete generated by my ORM are impredictible and this cause
> problem.

Apparently, you trust your ORM to delete everything, but it has bugs. This
shows the imporance of constraints, as it might not save everything or
delete
everything or make errors here and there.

Exactly why you should protect your database and your data.


Hey, it's all up to you, but if someone writes me a database application and
he says to value the data, but does not use constraints, I'm not going to
take
that application.


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

"Peter H. Coffin" <hellsop@ninehells.com> wrote in
news:slrnfml144.b1k.hellsop@abyss.ninehells.com:

> On Thu, 20 Dec 2007 11:48:10 +0100, Martijn Tonies wrote:
>> This is nothing but common sense. Read Peter H Coffins post, he
>> explains it in more detail.
>
> I do try...
>
> Some day, there'll be a mechanism to grant authorities to stored
> procedures instead of users, and life will really get fun. The long
> goal of being able to actually enforce things like "only User_X can
> delete rows with 'User_X' in column user_id" by granting DELETE to the
> procedure and taking it away from all users, forcing deletes through the
> SP, will actually be achievable. We can kinda do it with triggers but
> it's very messy.
>

Oracle has Fine Grain Access Control which provides this capability.