Re: Altering Table - Adding Primary Key With References

From: Anders Altberg (x_pragma_at_telia.com)
Date: 09/03/04

  • Next message: Mikhail: "Re: Altering Table - Adding Primary Key With References" 
    Date: Fri, 3 Sep 2004 10:08:51 +0200

    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

    "Mikhail" <Mikhail@discussions.microsoft.com> wrote in message
    news:8014F7E0-D462-44A9-A58C-BC702530ED4E@microsoft.com...
    > Can i, using alter table command, add primary key, which refers to a field
    in
    > other table?
    >
    > VFP Help Citation:
    >
    > 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
    :
    >
    > VFP Help Citation:
    >
    > CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]
    > ( FieldName1
    > ...
    > [PRIMARY KEY | UNIQUE [COLLATE cCollateSequence]]
    > [REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS])
    >

  • Next message: Mikhail: "Re: Altering Table - Adding Primary Key With References"

    Relevant Pages

    • Re: How do I discover a tables primary key?
      ... sp_helpconstraints system procedure in isql the result looks like ... an index once I have an entry in sysindexes. ... -- Number of references made by this table: ... from within my program I only get back one row, the PRIMARY KEY ...
      (comp.databases.sybase)
    • Re: How do I discover a tables primary key?
      ... calling sp_helpconstraint from isql gives different ... -- Number of references made by this table: ... But when I execute the same statement ("sp_helpconstraint City") ... from within my program I only get back one row, the PRIMARY KEY ...
      (comp.databases.sybase)
    • Re: Access as a RDBMS--why the multiple relationships?
      ... non-unique field in TABLE B. In the relationship diagram this shows ... referenced field is not a primary key, ... (CompositePartNo Integer references PARTS.PartNo, ... ComponentPartnentNo Integer references PARTS.PartNo, ...
      (comp.databases.theory)
    • Re: How do I discover a tables primary key?
      ... -- Number of references made by this table: ... But when I execute the same statement ("sp_helpconstraint City") ... from within my program I only get back one row, the PRIMARY KEY ... Why does the system procedure return ...
      (comp.databases.sybase)
    • Re: Altering Table - Adding Primary Key With References
      ... ALTER TABLE Morechildren ADD PRIMARY KEY cid TAG cid REFERENCES Parents TAG id ... VFP does not support PRIMARY KEY constraint with TAG and REFERENCES ...
      (microsoft.public.fox.vfp.dbc)
    Loading