Altering Table - Adding Primary Key With References

[Sietse Wijnker]

Hi Mikhail,

I think you're expecting too much functionality from the command

ADD PRIMARY KEY eExpression3 [FOR lExpression4] TAG TagName2
This clause in the alter table command gives you the possibility to add a
primary key _INDEX_(!)

PRIMARY KEY in the CREATE TABLE command also sets the field as a primairy
index.

Relations between tables can be defined in the database but they are not
defaulted when you open the tables using the USE command. The relations
defined in the database are used for default relations when adding the
tables to the dataenvironment of te form or report designer.
The relations are also used by the referential integrety.

HTH,
Sietse Wijnker

[Dan Freeman]

In addition to what Sietse has to say, if the field is in another table it
is by definition a FOREIGN KEY, not a PRIMARY KEY.

Dan

Can i, using alter table command, add primary key, which refers to a
field in other table?
ALTER TABLE TableName1 [DROP [COLUMN] FieldName3]
...
[ADD PRIMARY KEY eExpression3 [FOR lExpression4] TAG TagName2
[COLLATE cCollateSequence]]
In CREATE TABLE a can add primary key, which refers to field in
another table, but can't specify TAG for field which name is longer
CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]
( FieldName1
...
[PRIMARY KEY | UNIQUE [COLLATE cCollateSequence]]
[REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS])

[Mikhail]

You don't understand me, may be because my english is not so good :-)

In identity relashionship primary key from parent table migrate in child
table as primary key.

VFP:

If in parent table field name for PK not longer 10 char's i can use
following code:

CREATE TABLE ChildTable ( ;
ChildTablePrimaryKey I PRIMARY KEY REFERENCES ParentTable, ;
....
)
but if name of field is longer 10 char's a erroк "Tag Name is too long" is
occured.

In next example i can specify TAG for PRIMARY KEY but can't specify REFRENCE

CREATE TABLE ChildTable ( ;
ChildTablePrimaryKey I, ;
....
PRIMARY KEY ChildTablePrimaryKey TAG ChildTabPK
)

In ALTER TABLE command works similarly.

Post by Sietse Wijnker
Hi Mikhail,
I think you're expecting too much functionality from the command
ADD PRIMARY KEY eExpression3 [FOR lExpression4] TAG TagName2
This clause in the alter table command gives you the possibility to add a
primary key _INDEX_(!)
PRIMARY KEY in the CREATE TABLE command also sets the field as a primairy
index.
Relations between tables can be defined in the database but they are not
defaulted when you open the tables using the USE command. The relations
defined in the database are used for default relations when adding the
tables to the dataenvironment of te form or report designer.
The relations are also used by the referential integrety.
HTH,
Sietse Wijnker

[Sietse Wijnker]

Hi Mikhail,

You are (and i was) overlooking some additional clauses of the CREATE TABLE
command.
<some clauses snipped out/>
CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]
( FieldName1 FieldType [( nFieldWidth [, nPrecision] )] [NULL | NOT
NULL]
[, FieldName2 ... ]
[, PRIMARY KEY eExpression2 TAG TagName2]
[, FOREIGN KEY eExpression4 TAG TagName4 REFERENCES TableName3 [TAG
TagName5]])

the same goes for the ALTER TABLE command
<some clauses snipped out/>
ALTER TABLE TableName1
[ADD PRIMARY KEY eExpression3 [FOR lExpression4] TAG TagName2
[ADD FOREIGN KEY [eExpression5] [FOR lExpression6] TAG TagName4
REFERENCES TableName2 [TAG TagName5]]

Sample code:
Parent table testparent references testchild through use of the index on
tch_tpr_ifk (TAG tpr_ifk)

CREATE TABLE testParent (;
tpr_ipk_longerthan10 I AUTOINC, ;
tpr_cvalue C(10), ;
PRIMARY KEY tpr_ipk_longerthan10 TAG tpr_ipk)


CREATE TABLE testChild (;
tch_ipk I AUTOINC, ;
tch_tpr_ifk I, ;
tch_cvalue C(10), ;
PRIMARY KEY tch_ipk TAG tch_ipk, ;
FOREIGN KEY tch_tpr_ifk TAG tpr_ifk REFERENCES testParent TAG tpr_ipk )

Regards,
Sietse Wijnker

Post by Mikhail
You don't understand me, may be because my english is not so good :-)
In identity relashionship primary key from parent table migrate in child
table as primary key.
If in parent table field name for PK not longer 10 char's i can use
CREATE TABLE ChildTable ( ;
ChildTablePrimaryKey I PRIMARY KEY REFERENCES ParentTable, ;
....
)
but if name of field is longer 10 char's a erro? "Tag Name is too long"
is
occured.
In next example i can specify TAG for PRIMARY KEY but can't specify REFRENCE
CREATE TABLE ChildTable ( ;
ChildTablePrimaryKey I, ;
....
PRIMARY KEY ChildTablePrimaryKey TAG ChildTabPK
)
In ALTER TABLE command works similarly.

[Olaf Doschke]

Post by Sietse Wijnker
CREATE TABLE testParent (;
tpr_ipk_longerthan10 I AUTOINC, ;
tpr_cvalue C(10), ;
PRIMARY KEY tpr_ipk_longerthan10 TAG tpr_ipk)
CREATE TABLE testChild (;
tch_ipk I AUTOINC, ;
tch_tpr_ifk I, ;
tch_cvalue C(10), ;
PRIMARY KEY tch_ipk TAG tch_ipk, ;
FOREIGN KEY tch_tpr_ifk TAG tpr_ifk REFERENCES testParent TAG tpr_ipk )

I think you rather want a 1:1 relationship, where the primary key
references to the main table. This time not parent but brother or
sister table...

so in this case you would like:
CREATE TABLE testSister1 (;
ts1_ipk_longerthan10 I AUTOINC, ;
ts1_cvalue C(10), ;
PRIMARY KEY ts1_ipk_longerthan10 TAG ts1_ipk)

CREATE TABLE testSister2 (;
ts2_ipk I, ;
ts2_cvalue C(10), ;
PRIMARY KEY ts2_ipk TAG ts2_ipk;
REFERENCES testSister1 TAG ts1_ipk )

Well, this is not possible, as referencing keys
are always foreign keys. But you can make an
potential/candidate index on that foreign key, which
I think would better reflect the situation that the
primary key is generated in testSister1 with
AUTOINC and not in testSister2, so you don't
really have a primary key in testSister2.

therefore use:
CREATE TABLE testSister2 (;
ts2_ifk_looooongname I, ;
ts2_cvalue C(10), ;
FOREIGN KEY ts2_ifk_looooongname TAG ts2_ifk NODUP;
REFERENCES testSister1 TAG ts1_ipk )

note the NODUP option....

But you are right:
With ALTER TABLE you can define a field
being a primary key referencing a (primary) key
of another table, but you cannot give a TAG
name for that primary key, so the field name
must be maximum 10 characters long, as it is
taken for the tagname...

Bye, Olaf.

[Anders Altberg]

If you want the relation to be a one-to-one instread of one-to-many, you can
let the primary key of the Child table be the referecing field. But you
can't do it with the ALTER TABLE command in VFP8. You can do it in the
CREATE TABLE command. And you can just set up the reference in the Database
Designer window by drag-and-drop.
These commands work:
CREATE DATABASE Test
CREATE TABLE Parents (id i NOT null PRIMARY KEY autoinc)
CREATE TABLE Children (id i NOT null PRIMARY KEY REFERENCES Parents TAG id)
CREATE TABLE morechildren (cid Integer NOT NULL )
ALTER TABLE Morechildren ADD FOREIGN KEY cid TAG cid1 REFERENCES Parents
TAG id
ALTER TABLE Morechildren ADD PRIMARY KEY cid TAG cid REFERENCES Parents TAG
id
ALTER TABLE Morechildren ADD CANDIDATE cid TAG cid REFERENCES Parents TAG id
CANDIDATE is a synonym for UNIQUE.
Please note that the foreign key creation doesn't set up referential
integrity. You have to go to RI Builder to do that.
-Anders

Can i, using alter table command, add primary key, which refers to a field

in

other table?
ALTER TABLE TableName1 [DROP [COLUMN] FieldName3]
...
[ADD PRIMARY KEY eExpression3 [FOR lExpression4] TAG TagName2
[COLLATE cCollateSequence]]
In CREATE TABLE a can add primary key, which refers to field in another
table, but can't specify TAG for field which name is longer that 10 chars
CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]
( FieldName1
...
[PRIMARY KEY | UNIQUE [COLLATE cCollateSequence]]
[REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS])

[Mikhail]

While attempting to execute command

ALTER TABLE Morechildren ADD PRIMARY KEY cid TAG cid REFERENCES Parents TAG id

in VFP5,VFP6,VFP9 error message "Syntax error" is generated.

!!! VFP does not support PRIMARY KEY constraint with TAG and REFERENCES
keyword's at same command. (CREATE TABLE, ALTER TABLE) !!!

For future discus pls refer example image
Loading Image...

Post by Anders Altberg
If you want the relation to be a one-to-one instread of one-to-many, you can
let the primary key of the Child table be the referecing field. But you
can't do it with the ALTER TABLE command in VFP8. You can do it in the
CREATE TABLE command. And you can just set up the reference in the Database
Designer window by drag-and-drop.
CREATE DATABASE Test
CREATE TABLE Parents (id i NOT null PRIMARY KEY autoinc)
CREATE TABLE Children (id i NOT null PRIMARY KEY REFERENCES Parents TAG id)
CREATE TABLE morechildren (cid Integer NOT NULL )
ALTER TABLE Morechildren ADD FOREIGN KEY cid TAG cid1 REFERENCES Parents
TAG id
ALTER TABLE Morechildren ADD PRIMARY KEY cid TAG cid REFERENCES Parents TAG
id
ALTER TABLE Morechildren ADD CANDIDATE cid TAG cid REFERENCES Parents TAG id
CANDIDATE is a synonym for UNIQUE.
Please note that the foreign key creation doesn't set up referential
integrity. You have to go to RI Builder to do that.
-Anders

Can i, using alter table command, add primary key, which refers to a field

in

other table?
ALTER TABLE TableName1 [DROP [COLUMN] FieldName3]
...
[ADD PRIMARY KEY eExpression3 [FOR lExpression4] TAG TagName2
[COLLATE cCollateSequence]]
In CREATE TABLE a can add primary key, which refers to field in another
table, but can't specify TAG for field which name is longer that 10 chars
CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]
( FieldName1
...
[PRIMARY KEY | UNIQUE [COLLATE cCollateSequence]]
[REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS])

[Anders Altberg]

Mikhail
Yes I forgot to add this information above the last two ALTER TABLE commands
in my example.
These work:
CREATE TABLE Children (id i NOT null PRIMARY KEY REFERENCES Parents TAG id)
ALTER TABLE Morechildren ADD FOREIGN KEY cid TAG cid1 REFERENCES Parents
TAG idThese do not work:
These do not work:
ALTER TABLE Morechildren ADD PRIMARY KEY cid TAG cid REFERENCES Parents TAG
id
ALTER TABLE Morechildren ADD CANDIDATE cid TAG cid REFERENCES Parents TAG
id

Connecting two primary keys can always be done manually with drag&drop in
the Database Designer.
Sorry for the confusion.
-Anders

Post by Mikhail
While attempting to execute command
ALTER TABLE Morechildren ADD PRIMARY KEY cid TAG cid REFERENCES Parents TAG id
in VFP5,VFP6,VFP9 error message "Syntax error" is generated.
!!! VFP does not support PRIMARY KEY constraint with TAG and REFERENCES
keyword's at same command. (CREATE TABLE, ALTER TABLE) !!!
For future discus pls refer example image
http://www.intell-corp.ru/img/example.jpg

Post by Anders Altberg
If you want the relation to be a one-to-one instread of one-to-many, you can
let the primary key of the Child table be the referecing field. But you
can't do it with the ALTER TABLE command in VFP8. You can do it in the
CREATE TABLE command. And you can just set up the reference in the Database
Designer window by drag-and-drop.
CREATE DATABASE Test
CREATE TABLE Parents (id i NOT null PRIMARY KEY autoinc)
CREATE TABLE Children (id i NOT null PRIMARY KEY REFERENCES Parents TAG id)
CREATE TABLE morechildren (cid Integer NOT NULL )
ALTER TABLE Morechildren ADD FOREIGN KEY cid TAG cid1 REFERENCES Parents
TAG id
ALTER TABLE Morechildren ADD PRIMARY KEY cid TAG cid REFERENCES Parents TAG
id
ALTER TABLE Morechildren ADD CANDIDATE cid TAG cid REFERENCES Parents TAG id
CANDIDATE is a synonym for UNIQUE.
Please note that the foreign key creation doesn't set up referential
integrity. You have to go to RI Builder to do that.
-Anders

Can i, using alter table command, add primary key, which refers to a field

in

other table?
ALTER TABLE TableName1 [DROP [COLUMN] FieldName3]
...
[ADD PRIMARY KEY eExpression3 [FOR lExpression4] TAG TagName2
[COLLATE cCollateSequence]]
In CREATE TABLE a can add primary key, which refers to field in another
table, but can't specify TAG for field which name is longer that 10 chars
CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]
( FieldName1
...
[PRIMARY KEY | UNIQUE [COLLATE cCollateSequence]]
[REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS])

[Anders Altberg]

A correction. The CREATE TABLE command work fine. The first ALTER TABLE in
my examples also works as expected.
The last two do not work, which is what I wanted to show.
-Anders

Post by Anders Altberg
If you want the relation to be a one-to-one instread of one-to-many, you can
let the primary key of the Child table be the referecing field. But you
can't do it with the ALTER TABLE command in VFP8. You can do it in the
CREATE TABLE command. And you can just set up the reference in the Database
Designer window by drag-and-drop.
CREATE DATABASE Test
CREATE TABLE Parents (id i NOT null PRIMARY KEY autoinc)
CREATE TABLE Children (id i NOT null PRIMARY KEY REFERENCES Parents TAG id)
CREATE TABLE morechildren (cid Integer NOT NULL )
ALTER TABLE Morechildren ADD FOREIGN KEY cid TAG cid1 REFERENCES Parents
TAG id
ALTER TABLE Morechildren ADD PRIMARY KEY cid TAG cid REFERENCES Parents TAG
id
ALTER TABLE Morechildren ADD CANDIDATE cid TAG cid REFERENCES Parents TAG id
CANDIDATE is a synonym for UNIQUE.
Please note that the foreign key creation doesn't set up referential
integrity. You have to go to RI Builder to do that.
-Anders

Can i, using alter table command, add primary key, which refers to a field

in

other table?
ALTER TABLE TableName1 [DROP [COLUMN] FieldName3]
...
[ADD PRIMARY KEY eExpression3 [FOR lExpression4] TAG TagName2
[COLLATE cCollateSequence]]
In CREATE TABLE a can add primary key, which refers to field in another
table, but can't specify TAG for field which name is longer that 10 chars
CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]
( FieldName1
...
[PRIMARY KEY | UNIQUE [COLLATE cCollateSequence]]
[REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS])