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)

 

[SSAAHW]

Olaf, Mike Sorry I could not reply you for a long period.
Stepping a few step back in this discussion I begin once again from where I had created two tables using-

CREATE TABLE K:\P4\Saurabh\Tbl\TestDept ;
(DeptID n(3) NOT Null, ;
DeptName c(40) Not Null, ;
HOD c(40) NOT Null, ;
PRIMARY KEY DeptID TAG DeptID)

&

CREATE TABLE K:\P4\Saurabh\Tbl\TestEmp ;
(Eid n(3) NOT Null Unique, ;
EName c(40) NOT Null, ;
Salary currency(6) Null DEFAULT 0, ;
Gender c(1) NOT Null CHECK Gender = 'M' OR Gender = 'F', ;
DOB d(8) NOT Null, ;
DeptID n(3) REFERENCES TestDept TAG DeptID, ;
PRIMARY KEY Eid TAG pnEid)

I attached both tables into a database Samir.dbc and the relationship between both tables is clearly visible there.
Now I copied the tables one by one into another database named Test.dbc using following commands-

Use K:\P4\Saurabh\Tbl\TestDept.dbf
Copy To K:\P4\Saurabh\TestDept.dbf DATABASE K:\P4\Sarabh\Test.dbc

Use K:\P4\Saurabh\Tbl\TestEmp.dbf
Copy To K:\P4\Saurabh\TestEmp.dbf DATABASE K:\P4\Sarabh\Test.dbc

Tables are copied into the database but there is no relationship between the two.
How can I copy the tables (in relation - Primary Key Foreign Key, or Referencing) to another database with all keys and indices?

 

[Olaf Doschke]

Well,

1. I never said you start with an newly created DBC for your new tables.
2. I proposed several solutions and you seem to mix them into one.

Let's restart and give you one solution to use carefully, but simple to do
1. Have the original DBC, either the initial state of your customer or your own development database, you should have the latest DBC version of course. Not the latest data, just the latest structure is enough.
2. Add your tables, via code, manually, as you like.
3. To adjust the production DBC, overwrite its DBC/DCT/DCX files with your modified DBC, add new DBF files. Just on the level of file copies, no fancy code, no ADD TABLE, no COPY TO, nothing like that, just add the new/modified files.

The DBC should not change while using the application, so overwriting it you just integrate all your new meta data, names, paths, relations, stored procs, triggers, rules, etc into production, just via overwriting the DBC files. As you only need new DBFs you just need to add your new files into the dbf folder(s).

This solution is simple, but has the risc to fail on applications modifying the DBC regularly eg by defining views, daily/weekly/monthly/yearly tables. That should not be the case. Also this of course doesn't work for restructuring existing DBFs, adding single fields, etc. For that you'd need the a solution copying data into new but empty dbfs.

You always have to take into account the production DBC and its DBFs continue to change datawise, while you develop, so the old data has to remain. The other proposed solution would copy over all current data via APPEND into empty new DBFs, all DBFs, not just your two new ones, but has a catch with triggers and referential integrity during that copy, from that perspective just adding your new DBFs and DBC is less risky, as it keeps existing DBFs in their current state.

Every solution has its pros and cons. It's not a simple process to modify a DBC in the ambition to do so seamless with all current data. As you don't seem to have ever done anything like that before, perhaps the most safe way would be to make use of the stonefield database toolkit coming with good documentation and all instructions you need.

Anyway you do it, first do so on a copy of course, and keep a backup you just create in time short before the modifications, obviously. Well, that's a very general rule you surely follow for anything anyway, not related to DBCs or even just FoxPro.

Bye, Olaf.