Create Table involving Primary Key, Null, Not Null and References 2

[SSAAHW]

I have written following create table structures-
1.
Create Table Dept ;
(DeptID n(3) Primary Key, ;
DeptName c(40) Not Null, ;
HOD c(40) Not Null)

and

2.
Create Table emp ;
(Eid n(3) Primary Key
EName C(40) Not Null, ;
Gender l(1) Null Check Gender = 'M' Or Gender = 'F', ;
Salary n(6) Null Default 0, ;
DOB d(8) Not Null, ;
DeptID n(3) References Dept DeptID)

The first worked well but the second did not work giving syntax error message.
The field DeptID in the first table is Primary key whereas that in second table is foreign key.

I was learning to use primary key, foreign key in the table.

The error comes when I include following -
- Salary n(6) Null Default 0, and
- DeptID n(3) References Dept DeptID)

 

[Mike Lewis]

The most obvious fault is in the second line:

[tt](Eid n(3) Primary Key[/tt]

There is no comma after this clause, and no semi-colon to serve as line terminator. Assuming those errors didn't just creep in when you copied your code into your message, that would explain the syntax error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Also, in the last line:

[tt]DeptID n(3) References Dept DeptID)[/tt]

I would put the keyword TAG immediately before the primary key field name:

[tt]DeptID n(3) References Dept TAG DeptID)[/tt]

I don't know whether that is compulsory or not (the syntax diagram in the Help page suggests that it is), but it won't take you a moment to try it.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SSAAHW]

Thank you for the quick reply.
Actually that is my mistake in copying and pasting the code. Please ignore that.
I tried checking the code including lines one by one. The error comes when I include any of the following two lines of codes-
- Salary n(6) Null Default 0, and
- DeptID n(3) References Dept DeptID)

 

[SSAAHW]

That worked. Adding TAG did solve the error in the 2nd line.

 

[Olaf Doschke]

>DeptID n(3) References Dept DeptID
The syntax explanation is: [REFERENCES TableName2 [TAG TagName1]], Mike already said you forgot TAG, the whole clause [TAG Tagname1] is optional, but if you specify a tag name, you have to introduce that with the TAG, that's why in this syntax diagram the square brackets (which always denote optional parts are around both [TAG Tagname1], so either you specify TAG DeptID or nothing, but not only DeptID.

To have more control about index names, which can differe from long field names, as they have the 10 chcarcter limit even for DBC tables, you should not use the PRIMARY KEY option at defining the primary key field, but have an extra clause [tt]...fielddefinitions..., PRIMARY KEY DeptID TAG pxDeptID[/tt]. It's a matter of taste, if you name indexes and fields differently, but in my own naming conventions extending the VFP naming conventions though of the limited index tag name length I give them prefixes to differentiate and later knwo and see the differrence of whether a field or it's index is addressed. Many misunderstand about functions and commands result from mixing up things by naming them identical. You only learn precise coding, if you give different things different names.

No idea what's wrong with your [tt]n(6) null default 0[/tt] field, you can define such a field. A currency field could be more appropriate, but depends on your situation. There is the annoyance of it being USD by default and it of course has no currency conversion and knowledge about conversion rates, but as you can configure the currency symbol and appearance left or right of the amount, you can also use the currency field type for any currency.

Overall, why don't you copy code, eg your Gender I(1) surely also should be C(1), there is no type I(1). You don't get any error here when just using the perhaps most fundamental thing for developers, technical users and even end users: The clipboard, copy&paste. We can only judge the real code, not what you type differently here than there.

Bye, Olaf.

 

[SSAAHW]

Thank you Olaf. With Currency datatype and Tag DeptID my table was successfully created.
Working through RDP, that's why couldn't copy and paste the code, Sorry it troubled you.
I was using logical L(1) data type but that was absurd with "Null Check Gender = 'M' Or Gender = 'F'", It was not giving error here but definitely not accept values after table creation.
Thank you to both of you.

 

[Mike Lewis]

I was using logical L(1) data type but that was absurd with "Null Check Gender = 'M' Or Gender = 'F'

Yes, I noticed the L(1) data type, and was wondering if it was worth pointing it out (it wouldn't have generated an error). But I missed the "absurd" NULL CHECK.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

This isn't a NULL CHECK, this is a NULLable field, with a CHECK constraint (field rule), What's bad about this is the NULLability, if you only want to allow "M" or "F" you have to disallow NULL: NOT NULL

You can Copy& Paste over RDP, just check the RDP connection properties and allow exchange via Clipboard.

Bye, Olaf.

 

[SSAAHW]

Truly said....

 

[SSAAHW]

Copy and Paste can paste can be done from RDP but due to security reasons it is restricted from the server side.

 

[SSAAHW]

Finally my modified structures are:
1.
Create Table Dept ;
(DeptID n(3) Not Null Unique, ;
DeptName c(40) Not Null, ;
HOD c(40) Not Null, ;
Primary Key DeptID Tag DeptID)

and

2.
Create Table emp ;
(Eid n(3) Not Null Unique, ;
EName c(40) Not Null, ;
Gender c(1) Null Check Gender = 'M' Or Gender = 'F', ;
Salary currency(6) Null Default 0, ;
DOB d(8) Not Null, ;
DeptID n(3) References Dept Tag DeptID, ;
Primary Key Eid Tag Eid)

 

[Olaf Doschke]

You still have your Gender field nullable. The check of Gender='M' OR Gender='F' will fail on NULL, try yourself and make yourself known to NULL arithmetics:

? .NULL.='M' OR .NULL.='F'

This yields .NULL., neither .T. nor .F.

You can't compare with .NULL., you have to either check via ISNULL() or you simply disallow that value overall by making the field NOT NULL.

You can also extend the Check rule to disallow .NULL. as a first check: NOT ISNULL(Gender) AND (Gender='M' OR Gender='F'), but it's simpler to set the field NOT NULLable.

Bye, Olaf.

 

[Olaf Doschke]

One more thing to note is, that the Reference you build up does not cause any referrential integrity rules the DBC checks. And that's just one reason, why overall I do not use DDL SQL language (CREATE TABLE, INDEX ON, ALTER TABLE, etc) to create or alter a database and its tables. You can visually design your database and later use GENDBC to create code to recreate the database empty elswhere or as startup or for creating as many yearly or client/principal/mandator databases you like. And the GENDBC routines also keep some info in a binary file, not everything is rebuilt with just code. Even that doesn't take care of file/folder access permissions to roundup the database as far as you can for a VFP database, so my main way of distributing a database is via its files in the end.

Bye, Olaf.

 

[Mike Lewis]

This isn't a NULL CHECK, this is a NULLable field, with a CHECK constraint (field rule)

That was just a slip of the pen on my part. I should have said that it was the CHECK constraint that was "absurd".

As for the nullability of the Gender field, I would have thought that was what you would want. It should be a character field, with values M (for male), F (for female) and NULL (for unknown, doubtful or 'prefer not to say').

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SSAAHW]

Yes Null arithmetic-

not NULL = NULL

NULL or false = NULL

NULL or true = true

NULL or NULL = NULL

NULL and false = false

NULL and true = NULL

NULL and NULL = NULL

So I will make the field Not Nullable.
Moreover, I got yet another good lesson not to use DDL SQL language commands - CREATE TABLE, INDEX ON, ALTER TABLE, etc to create or alter a database and its tables.

 

[Olaf Doschke]

Well, in regard of db scripts that's not a no go for every case, but indeed the file based nature of data makes it easy to do a table ALTERATION differently, eg

In your developer version of the DBC alter the table you want to alter. Use GENDBC to create a database generating script + extra binary file (procedures AFAIK) and let it run, APPEND customer data to the new structure and shift locations (MoveFile old DBC and newly created DBC). This seems overdone, but if you alter a table VFP indeed does nothing less, it does not really alter the existing DBF, it creates a new file, moves old data to the new dbf, deletes the old file and renames it.

The DBC itself can simply be replaced.

There's one major other way to go about database maintainance in the most professional way with a separate data dictionary about the database strucutre via the Stonefield Database Toolkit. It also introduces triggers to make note of the data dictionary changes both when you alter tables and views and no matter if via code or via designers, the DBC triggers will act on all changes. The dtta dictionary itself then is used to compare against the installed DB structure at a customer site and necessary code to get a database to a new structure is generated.

In the opposite site against my own choice, there is some clarity in defining a database merely with a script and let it run as initialisation, but you can get out of sync too easy, eg a certain ALTER TABLE you might later do for version 3.5.6 of your software will only work to upgrade version 3.5.5 and if a user is at version 2.1.0 it'll break things. It's a maintenance hell to write db updates as scripts. Even if you always execute all upgrade scripts cumulated you'll get in trouble with the one or other data constellation and have trouble with the users not following all your upgrade path step by step but skipping a few versions.

Last not least for an initial setup of a database nothing is easier to provide the dbc and dbf etc files in the setup, just ensure they are intact. You can then also easily provide some initial system data.

Bye, Olaf.

 

[Olaf Doschke]

In regard of NULL, if you would want to allow an unknown gender you could either have another letter "U" for that, or allow NULL and use the check rule [tt]ISNULL(Gender) OR Gender='M' OR Gender='F'[/tt]. In regard of choice and display and other things, I'd perhaps opt for the "U" letter instead of NULL, though NULL is the correct technical database value for unknown data.

Bye, Olaf.

 

[Mike Lewis]

In regard of NULL, if you would want to allow an unknown gender you could either have another letter "U" for that, or allow NULL

I don't disagree with that. There might also be a case for allowing both NULL and a letter such as "U".

NULL would mean 'unknown' in the sense that the information has not yet become available; the client is filling in a form with their personal details, and hasn't yet provided the relevant page; or perhaps the record has been created, but the personal information from the client has not yet been entered.

The letter "U" could mean that the client prefers not to disclose the information; or that the sex is really unknown in the sense that the person who gathered the information didn't get round to asking. These days, there might even be a case for another letter, say "O", to mean 'other'. (But it might be safer if we don't go down that road but rather keep the discussion at a technical level.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

>NULL would mean 'unknown' in the sense that the information has not yet become available; the client is filling in a form with their personal details, and hasn't yet provided the relevant page; or perhaps the record has been created, but the personal information from the client has not yet been entered.

In a bizobject I'd handle that as mandatory/optional fields, so if that info was mandatory you couldn't save it unspecified. If you go about data entry with a form bound to a new row you create via APPEND BLANK, you can easily revert that new record, buffering provided. Which doesn't leave you with anything you can't save, if it doesn't fit the business rules.

If you have very elaborate entities with many detail infos too much mandatory fields could be a maintenance nightmare for end users needing to specify all the info before being able to add a new record.

It's a nice simple rule to set optional fields nullable, because that's what they are technically, if you allow NULL, that's why I hesitate to tell you what you should do and instead give all the alternative solutions, this is not a technical issue, but a business rule decision. Another way to handle such optional info is to separate it into an extra table, but obviously you can also overdo by having an extra table for any optional info. It pays mostly, if it's an attribute you seldom have or enter or specify. I would say a gender is a thing you normally know right away, so it should rather be a mandatory field, on the other hand the gender of employees has a low importance, it should have, at least. Unless there is a female quota for your company, for example.

Bye, Olaf.