Results 1 to 10 of 10

Thread: Please help me with some SQL...

  1. #1
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb

    Please help me with some SQL...

    Hey guys,

    Basically we've all been set some crappy databases coursework, where we have to use mysql and jdbc to do some stuff... I've just downloaded mysql 4.1, but im having trouble creating a database and making a table with some stuff in it.

    This is an example of a .sql file given on the course website:

    Code:
    CREATE DATABASE library;
    USE library;
    
    CREATE TABLE T_Reader (
    	ID INTEGER NOT NULL PRIMARY KEY,
    	LastName VARCHAR ( 20 ) ,
    	FirstName VARCHAR ( 20 )
    	);
    
    CREATE TABLE T_Book (
    	LibID INTEGER NOT NULL PRIMARY KEY,
    	Title VARCHAR ( 255 ) NOT NULL,
    	Author VARCHAR ( 255 ) NOT NULL,
        Borrower INTEGER,
        Out DATE,
        FOREIGN KEY (Borrower) REFERENCES T_Reader
    	);
    
    CREATE TABLE T_ReadingList (
    	
    	Date DATE NOT NULL,
    	LibID INTEGER NOT NULL,
    	ID INTEGER NOT NULL,
    	PRIMARY KEY (LibID, ID, Date),
        FOREIGN KEY (LibID) REFERENCES T_Book,
        FOREIGN KEY (ID) REFERENCES T_Reader
    	);
    
    INSERT INTO T_BOOK (LibID,Title,Author)
    VALUES (1, 'Noddy', 'Bylton');
    INSERT INTO T_BOOK (LibID,Title,Author)
    VALUES (2, 'Harry Potter', 'Rowling');
    INSERT INTO T_BOOK (LibID,Title,Author)
    VALUES (3, 'Databases', 'Connolly');
    INSERT INTO T_BOOK (LibID,Title,Author)
    VALUES (4, 'The Mousetrap', 'Christie');
    Ive saved this as c:\comp2004\library.sql... if i load up the mysql terminal, whack in my password and type:

    Code:
    mysql> source c:\comp2004\library.sql
    Query OK, 1 row affected (0.00 sec)
    
    Database changed
    Query OK, 0 rows affected (0.00 sec)
    
    ERROR 1005 (HY000): Can't create table '.\library\t_book.frm' (errno: 150)
    ERROR 1005 (HY000): Can't create table '.\library\t_readinglist.frm' (errno: 150
    )
    ERROR 1146 (42S02): Table 'library.t_book' doesn't exist
    ERROR 1146 (42S02): Table 'library.t_book' doesn't exist
    ERROR 1146 (42S02): Table 'library.t_book' doesn't exist
    ERROR 1146 (42S02): Table 'library.t_book' doesn't exist
    It appears to make the t_reader table, but not the others. WHY!? I cant understand why it doesnt work... is it something to do with using 'windows' end of line characters rather than unix ones (ie because i wrote the file in notepad rather than vim or something?)

    help

  2. #2
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts
    error 150 is a forreign key problem
    http://forums.devarticles.com/mysql-...150t-7704.html
    (first one i found in google).

    as you can see when your defining forign keys, your nto saying WHICH key in the table, ie borrower is ment to be ID?
    throw new ArgumentException (String, String, Exception)

  3. #3
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb
    I assumed that if the lecturer had given us an example bit of SQL it would've worked. but he is a bellend like that... I'll try to make a few simple tables without relationships and see what happens, cheers buddy

  4. #4
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts
    yeah it looks like the sample script is incomplete
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  5. #5
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb
    yup, one of my mates has been working on this aswell, and by adding the corresponding names to the end of the 'foreign key' bits everything works, cheers guys

  6. #6
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    Load up wampserver from www.wampserver.com then log onto http://localhost/phpmyadmin and you can fire in the SQL in a web interface and get better feedback. In addition you can create the table manually and see the correct SQL. Another option once you have MySQL installed is to use MySQL Query Browser from http://dev.mysql.com/downloads/query-browser/1.1.html

  7. #7
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts
    Iain , not being too familiar with SQL in its MySQL guise - is phpmyadmin like enterprise manager ?
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  8. #8
    Gaarrrrr! Dav0s's Avatar
    Join Date
    Aug 2005
    Location
    Bristol
    Posts
    1,442
    Thanks
    1
    Thanked
    3 times in 3 posts
    phpmyadmin is a web based mysql manager thaty uses php to edit mysql databases.

  9. #9
    Senior Member
    Join Date
    Aug 2005
    Location
    Brisbane
    Posts
    612
    Thanks
    20
    Thanked
    35 times in 22 posts
    • toolsong's system
      • Motherboard:
      • ASUS P7P55D EVO
      • CPU:
      • i7 860 @ 3.8 GHz
      • Memory:
      • 2 x 4GB Corsair Vengeance
      • Storage:
      • SSDs
      • Graphics card(s):
      • GTX970
      • PSU:
      • Seasonic X650
      • Case:
      • Antec P180
      • Operating System:
      • Win7 x64
      • Monitor(s):
      • Dell U2515H
      • Internet:
      • Fibre @ 100/40
    I don't know MYSQL but a quick look at the DDL reveals your second table is being created with a FK to the first table on column Borrower, but this doesn't exist in the first table.

    ...OK I see you've worked it out, I guess by saying

    FOREIGN KEY (Borrower) REFERENCES T_Reader.ID

    In my book that would be bad practice for naming conventions. Your 'person' entity is called Reader in the parent table, Borrower in the child table and the main PK column as just 'ID' tells you nothing about that ID.

    Clean it up by changing the first table to have a PK of T_Reader.Reader_ID and the FK to the child represented by column T_Book.Reader_ID.

    Hope that makes sense

  10. #10
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb
    its a crap example from a crap lecturer i'm going to use decent names for stuff in the actual coursework

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Member Database SQL / PHP
    By realm in forum Software
    Replies: 4
    Last Post: 03-01-2006, 06:48 PM
  2. SQL Server 2000
    By Fatboy in forum Software
    Replies: 37
    Last Post: 17-08-2005, 07:05 PM
  3. SQL Server Connection string?
    By Stoo in forum Software
    Replies: 7
    Last Post: 04-05-2005, 05:48 PM
  4. SQL Connection
    By Raz316 in forum Software
    Replies: 2
    Last Post: 11-08-2004, 06:58 PM
  5. Sql
    By Lead_Head in forum Software
    Replies: 13
    Last Post: 03-12-2003, 10:09 PM

Posting Permissions

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