Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2005
    Posts
    6

    Unanswered: Trying to create three table relational db

    Howdy, I'm just experimenting with SQL and Access and would like to know what is wrong with my "booking" table and how I can make it relational with those constraints. I suspect this to be an obvious thing, if anyone can help that'd be sweet

    By the way, is there anything else in this SQL that you would change? Like do you usually use the "AUTOINCREMENT" with the Primary Key? Also is the code formatted as it should be?

    Code:
    CREATE TABLE customer (
    [custId] AUTOINCREMENT(100000,1)
    CONSTRAINT PK_customer PRIMARY KEY,
    [custFname] TEXT,
    [custSname] TEXT,
    [custPhone] TEXT,
    [custMob] TEXT,
    [custStAddress] TEXT,
    [custSuburb] TEXT,
    [custCity] TEXT,
    [custPcode] TEXT,
    [custEmail] TEXT
    );
    
    CREATE TABLE booking (
    [bookId] AUTOINCREMENT(100000,1)
    CONSTRAINT PK_booking PRIMARY KEY,
    [bookArrival] DATE,
    [bookDeparture] DATE,
    [bookPayment] DATE,
    [bookCost] CURRENCY,
    CONSTRAINT FK_customer FOREIGN KEY (custId) REFERENCES customer (custId),
    CONSTRAINT FK_cottage FOREIGN KEY (cottId) REFERENCES cottage (cottId)
    );
    
    CREATE TABLE cottage (
    [cottId] AUTOINCREMENT
    CONSTRAINT PK_cottage PRIMARY KEY,
    [cottName] TEXT,
    [cottCapacity] NUMBER,
    [cottDescription] MEMO
    );
    Last edited by toothpick; 05-23-07 at 11:16.

  2. #2
    Join Date
    Apr 2005
    Posts
    6
    Also how would you add say the letter "c" as a prefix to the primary key auto number? eg c105837

    thanks

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by toothpick
    Also how would you add say the letter "c" as a prefix to the primary key auto number? eg c105837
    The real question is; why would you want to do that?
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The general point that georgev is trying to make, I think, is that Autonumbers are really intended for internal "housekeeping" by Access, not to be used for identification purposes, which, unfortunately many people try to do. Access' generation of them is very capricious, and deleted records, discarded partial records, etc. all go towards making them unreliable for this purpose. If you need an ID number with the prefix C you need to generate this yourself.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Apr 2005
    Posts
    6
    Ok that makes sense, thanks

    What code do you need to put in the booking table to make the foreign keys work?

  6. #6
    Join Date
    Apr 2005
    Posts
    6
    Ok here's my updated SQL. Now I only have to figure out what's wrong with the cascade update and delete statement

    Code:
    CREATE TABLE customer (
    [custId] AUTOINCREMENT(150,1)
    CONSTRAINT PK_customer PRIMARY KEY,
    [custFname] TEXT,
    [custSname] TEXT,
    [custPhone] TEXT,
    [custMob] TEXT,
    [custStAddress] TEXT,
    [custSuburb] TEXT,
    [custCity] TEXT,
    [custPcode] TEXT,
    [custEmail] TEXT
    );
    
    CREATE TABLE booking (
    [bookId] AUTOINCREMENT(150,1)
    CONSTRAINT PK_booking PRIMARY KEY,
    [custId] INTEGER,
    [cottId] INTEGER,
    [bookArrival] DATE,
    [bookDeparture] DATE,
    [bookPayment] DATE,
    [bookCost] CURRENCY,
    CONSTRAINT FKcustId FOREIGN KEY (custId) REFERENCES customer ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT FKcottId FOREIGN KEY (cottId) REFERENCES cottage ON UPDATE CASCADE ON DELETE CASCADE
    );
    
    CREATE TABLE cottage (
    [cottId] INTEGER
    CONSTRAINT PK_cottage PRIMARY KEY,
    [cottName] TEXT,
    [cottCapacity] NUMBER,
    [cottDescription] MEMO
    );

  7. #7
    Join Date
    Apr 2005
    Posts
    6
    Ok I've been looking for more tutorials and it seems like cascade update and delete is unnecessary with auto numbers. Is this correct?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nope
    cascade determines how child records, ie rows which are dependent on a value in another table are are handled when the parent row changes or is deleted.
    so you are right to suggest that a cascade update is uneccessary, but a cascade delete may be relevant

    cascade update is irrelevant here because you are not going to change the value of the primary key in the parent table as you correctly point out is an autogenerated column.

    however cascade delete is designed to handle the deletion of the parent record triggering all child rows also to be deleted. so that's application specific. say you had a telesales order processing form.. the customer gives you their order.. subsequently for what ever reason the customer calls back to cancel the order. if you have delete cascade then deleting the order deletes the order and everything dependent on that PK. otherwise you woulkd have to go through each and every order item and delete that.

    so as ever it depends on what you as designer thinks is appropriate for your application
    cascade operations can be usefull.. but theres nothing magical about them, you can get the same results by using a bit of SQL. Indeed I expect thats all that cascade events are... it fires a trigger that runs some SQL and takes appropriate action (either deleting or amending child records as required).
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by toothpick
    Ok here's my updated SQL. Now I only have to figure out what's wrong with the cascade update and delete statement
    one thing to notice is that when the parser gets to the point of trying to interpret your foreign key declarations, one of them references a table that doesn't exist yet

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2005
    Posts
    6
    Thanks for the advice I've made some more changes and it's working pretty good. Just that I can't seem to set a field size limit on the NUMBER fields as with the text fields

    Code:
    CREATE TABLE customer (
    [custId] AUTOINCREMENT(150,1)
    CONSTRAINT PK_customer PRIMARY KEY,
    [custFname] TEXT (30),
    [custSname] TEXT (30),
    [custPhone] NUMBER,
    [custMob] NUMBER,
    [custStAddress] TEXT (40),
    [custSuburb] TEXT (20),
    [custCity] TEXT (20),
    [custPcode] NUMBER,
    [custState] TEXT (3),
    [custEmail] TEXT (50),
    [custNote] MEMO
    );
    
    CREATE TABLE booking (
    [bookId] AUTOINCREMENT(150,1)
    CONSTRAINT PK_booking PRIMARY KEY,
    [custId] INTEGER,
    [cottId] INTEGER,
    [bookArrival] DATE,
    [bookDeparture] DATE,
    [bookPayment] TEXT (20),
    [bookCost] CURRENCY,
    CONSTRAINT FK_custId FOREIGN KEY (custId) REFERENCES customer,
    CONSTRAINT FK_cottId FOREIGN KEY (cottId) REFERENCES cottage
    );
    
    CREATE TABLE cottage (
    [cottId] INTEGER
    CONSTRAINT PK_cottage PRIMARY KEY,
    [cottName] TEXT (40),
    [cottCapacity] NUMBER,
    [cottDescription] MEMO
    );

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't see NUMBER listed as one of the datatypes you can specify here --
    http://support.microsoft.com/kb/q180841/

    of course, that's access 2000, you might be on something later
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    i don't see NUMBER listed as one of the datatypes you can specify here --
    http://support.microsoft.com/kb/q180841/

    of course, that's access 2000, you might be on something later
    There is a difference between Access DDL and JET DDL you can execute through ADO (enough TLAs for ya? ).

    http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx
    Number == Double
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is ADO? is it like ODA? what about DAO? is that like DOA?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    what is ADO? is it like ODA? what about DAO? is that like DOA?
    ADO = Another Damn Objectclass
    ODA was I think a character in The Hobbit.
    DAO is deprecated (kind of) so yeah it is sort of DOA.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •