MS engine stop thinking (1 Viewer)

ahamied · Oct 9, 2015

when we've two tables with 1 => many relation
primary key in the master table won't be null.
So, the foreign field in the the foreign table won't be null.

But in my example :
1-primary table has no record.
2-foreign table has many records.
3-you can add records without has a primary value.

it's not a test for experts ....
need you opinion

Regards

JHB · Oct 9, 2015

ahamied said:
when we've two tables with 1 => many relation
primary key in the master table won't be null.
So, the foreign field in the the foreign table won't be null.

You haven't set it up correct - so the above statement is the truth with modifications.
Try it now - look at the relation.

ahamied · Oct 9, 2015

thank you JHB

according to Database normalization, what happen in my mdb file , mustn't happen
can explain why that happened , and how ?

may you download the original file again.
I know how to correct it.
but that should never occur in RDBMS
can you upload a sample like my file ?
how to add fields in the foreign table without restriction of primary table?

friend , may you modify your mdb and change some words you wrote , I don't like it

Ahmed

HiTechCoach · Oct 9, 2015

ahamied said:
when we've two tables with 1 => many relation
primary key in the master table won't be null.
So, the foreign field in the the foreign table won't be null.

The So... is not true.

The foreign field in the the foreign table cab be null even is the primary key in the master table won't be null.

ahamied said:
But in my example :
1-primary table has no record.
2-foreign table has many records.
3-you can add records without has a primary value.

That is the correct use of Null in the foreign key fields.

I am not at a computer where it is safe to download you example and look at it.

From what you are describing it sounds like you need to adjust the field properties for the foreign key field. If you want to require a record in the master table before entering records in the child/related table then you need to set the foreign key field to not allow nulls and be sure referential integrity is turned on. Or you have to write VBA code to do the validation.

HiTechCoach · Oct 9, 2015

Ahmed,

ahamied said:
according to Database normalization, what happen in my mdb file , mustn't happen

That is not true. It can happen. I use it with other RDMS when you properly use Null.

ahamied said:
can explain why that happened , and how ?

I think you are have not learned the power of Null.

If you are wanting to enforce referential integrity then you do not what orphaned records.

An orphaned record is when is when the foreign key field IS NOT NULL and the value does not exit in a record as the primary key in the related table.

When the foreign key has a NULL value it is not orphaned. It is just not assigned to a record in the related table.

In many cases you want to enter the master/parent table record before entering the related/child records. To do this you can use the database engine by setting the foreign key field's properties to not allow Nulls and turn on referential integrity. If you can't change the RDBMS then you can do it in the front end with code. Or both.

There are time when you want to enter records in the reverse order. This is where the child/related table records are entered before the master/parent table record is created.

ahamied · Oct 10, 2015

thank you Hi_Tech_Coach.

-Null ... the UNKOWN
-simply it's not a value , <> X/0 " X is a number" , it isn't an error ,and it's a logical fact. *Does Sam have a car? yes=1, No=0, or No answer given "Null may = silence "

-because it's a logical fact, it's not accepted in Binary, Hex , or even decimal systems.
-the problem is , our computer sciences based on that technology since IBM was using paper to store data ... before floppy drive 720K was invented

what's is other RDMS accept that ? till now it success only with mdb and MDF " the mother"

now I'm trying the same scenario with Ora11G , MySQL.

these fields were not null in the original source before using a tool to insert to MDB or MDF.
it's a matter of data converting.
158314032A<> 1583140321 <>NULL
158314032B<> 1583140322 <>NULL
158314032C<> 1583140323 <>NULL
158314032D<> 1583140324 <>NULL

we should agree that :
1-the primary must control foreign key behavior . Fact is not Math
2-any floor in a big building mustn't move one step to left. ( won't happen in a well protected RDBMS unless you touch FAT ) ...

wish you happy weekend

Galaxiom · Oct 10, 2015

ahamied said:
it's a matter of data converting.
1583140321 <>NULL

No it in't. A comparison operator (including "not equals") cannot be sensibly applied where one side is a Null. That expression will return False. The only operators that are valid with a Null are Is Null and Is Not Null.

we should agree that :
1-the primary must control foreign key behavior . Fact is not Math
2-any floor in a big building mustn't move one step to left. ( won't happen in a well protected RDBMS unless you touch FAT )

I have no idea what you are trying say but it appears you are blaming Access for a mistake in your design.

ahamied · Oct 10, 2015

[FONT=&quot]Not only access also [FONT=&quot]SQ server[/FONT] return the same result.[/FONT]
[FONT=&quot]I love access , I gained too much money because of access.[/FONT]
[FONT=&quot]I don't have mistaken in my design.[/FONT]
[FONT=&quot]primary key must force integrity to foreign key.[/FONT]
[FONT=&quot]I'm going to apply the same tool on Ora, and MySQL[/FONT]
[FONT=&quot]and maybe I'll change the OS.[/FONT]
[FONT=&quot]or even the processor.[/FONT]

[FONT=&quot]I'm not challenging anyone . I need support from expert like you.[/FONT]
[FONT=&quot]may you upload your own example contain data with the same mistake ? [/FONT]

[FONT=&quot]correction[/FONT]
[FONT=&quot]158314032[/FONT][FONT=&quot]A[/FONT][FONT=&quot]<> 158314032[/FONT][FONT=&quot]1[/FONT][FONT=&quot] not is NULL ----- or IS NOT Null
158314032[/FONT][FONT=&quot]B[/FONT][FONT=&quot]<> 158314032[/FONT][FONT=&quot]2 [/FONT][FONT=&quot]not is NULL ----- or IS NOT Null
158314032[/FONT][FONT=&quot]C[/FONT][FONT=&quot]<> 158314032[/FONT][FONT=&quot]3[/FONT][FONT=&quot] not is NULL ----- or IS NOT Null
158314032[/FONT][FONT=&quot]D[/FONT][FONT=&quot]<> 158314032[/FONT][FONT=&quot]4[/FONT][FONT=&quot] not is NULL ----- or IS NOT Null[/FONT]

[FONT=&quot]I like your word "cannot be sensibly applied" I'm sorry for using <>. this is a mistake.
Ahmed[/FONT]

stopher · Oct 10, 2015

ahamied said:
1-the primary must control foreign key behavior . Fact is not Math

You have this wrong. The table with the primary key does not "control" the foreign key behavior. You control the behavior of the foreign key by setting the constraints in the foreign keys table. This becomes more obvious if you create your tables using SQL because you create the "relationship" when you create the foreign key's table. And like HiTechCoach said, if you want the relationship to be mandatory then set the foreign key to Not Null.

It is quite acceptable for the relationship to be mandatory or optional (nulls allowed). Suppose you have a list of football players. Then you may well want to have the case where some players are not assigned to a team (the foreign key relating to the team table).

hth

ahamied · Oct 10, 2015

Stopher
thank you for explanation

what you said about teams and player, is true
but.....
what will the engine do when you try to insert a player belong to a team that is not included in your teams table ?
should the engine accept and ignore the new team ID and convert to null ?
or, should the engine refuse ? according to the integrity

according to the data being copied
If it's 1 record , the engine will prompt u an err msg.
if it's more than 150,000 record the engine silently will convert the new teamID to null
because it stop thinking

that's what I'm try to explain ... engine should take the same action regardless the number of records being copied

ahamied · Oct 10, 2015

Stopher
I attach 2 files
Access and excel files
copy all excel data from sheet 1 as it is
then paste it to MainData table

you'll be prompted that 11223 record was copied !!!!
engine automatically ignore many values " text " and convert these values to null

here is the problem
the engine must restrict me according to the relation.
and didn't accept players belong to other team not in my teams list

stopher · Oct 10, 2015

The problem stems from your your data is formatted in Excel. If you look down the Accounts column the you will see that most of the accounts are being treated by Excel as numbers. The ones with a letter on the end are treated as text. So when you copy/paste, then I guess the whole column is being treated as numeric and so the text values are being discarded. Access is doing what it is supposed to because it never gets to see the string accounts

If you convert the whole column to text then the copy/paste works fine and Access will give you a message saying it can't append a number of records 200+ if I remember rightly.

I also found that if I sorted the Excel data so the string accounts appear first then the copy/paste treats the whole column as text and again it all works fine.

hth
Chris

stopher · Oct 10, 2015

A poster here suggest that Access will determine the datatype by the first record in the copy/paste. Hence the problem. So make sure all the data is text.

ahamied · Oct 10, 2015

Chris.
So. There's no unique decision for the database engine
Without.
SQL server took the same decision.
Paste error table should be created.
So it's matter of data converting
Which makes database engine stop thinking
And didn't take the right decision .
Thank u Chris for understanding the idea.
Happy weekend Chris

ahamied · Oct 10, 2015

Finally .
According to data meanings concept , database engine took the wrong desision.
Ahmed

MS engine stop thinking (1 Viewer)

ahamied

New member

Attachments

JHB

Have been here a while

Attachments

ahamied

New member

HiTechCoach

Well-known member

HiTechCoach

Well-known member

ahamied

New member

Galaxiom

Super Moderator

ahamied

New member

stopher

AWF VIP

ahamied

New member

ahamied

New member

Attachments

stopher

AWF VIP

stopher

AWF VIP

ahamied

New member

ahamied

New member

Similar threads

Users who are viewing this thread