I need help on My SQL Database Creation Statement

asdfg · #1 03-Mar-2008, 19:58

Hi All

I am New to MySQL, I need a help on creation Statement for the my following statement(SQL).

Please check is it correctness, and please to suggest me the correct statement, I attached my Databases structure on pdf.

Followings are my owns.

For the Mmeber Table Creation:

Quote:

CREATE TABLE MEMBER(Member_ID CHAR (10) NOT NULL,University_ID CHAR (17),Name VARCHAR (100),Registration_Date DATE,Catergory CHAR(3) NOT NULL CHECK (Catergory IN ('ACS','NAS','LIB','GST'),Renewed_Date DATE,PRIMARY KEY (Member_ID) UNIQUE (University_ID),);

This is for the Book

Quote:

CREATE TABLE BOOK (ISBN CHAR(10) NOT NULL,TITLE VARCHAR(100),AUTHOR VARCHAR(100),PUBLICATION VARCHAR(100),PUBLICATION_DATE DATETIME,NUMBER_OF_COPY DECIMAL,PRIMARY KEY (ISBN));

This is for the Copies Table:

Quote:

CREATE TABLE COPIES (ACCESS_NO CHAR(6),ISBN CHAR(10) NOT NULL,RECEIVED_DATE DATE,AVAILABILITTY BOOLEAN NOT NULL DEFAULT 'TRUE',PRIMARY KEY (ACCESS_NO),FOREIGN KEY(ISBN) REFERENCES BOOK ON UPDATE CASCADE);

This Is for the Reserved Copy:

Quote:

CREATE TABLE RESERVED_COPY(Member_ID CHAR (10) NOT NULL,ISBN CHAR(10) NOT NULL, ISBN CHAR(10) NOT NULL, PRIMARY KEY (Member_ID,ISBN),FOREIGN KEY(ISBN) REFERENCES BOOK,MEMBER ON UPDATE CASCADE);

These are my statements, Only Book statement running others are giving the 150 error.

Please to solve my problem soon.

TurboPT · #2 03-Mar-2008, 20:09

There's a stray comma near the end of Member table creation...

I'll try the others...

TurboPT · #3 03-Mar-2008, 21:20

Minor changes...(compare to your original post), but basically:
1. member table (dropped unique at the end, append to University_ID)
2. copies (type boolean is converted to a tinyint; only takes 1, 0)
3. reserved_copy table (foreign key change, column 'ISBN' listed twice)

Code:

CREATE TABLE MEMBER(Member_ID CHAR (10) NOT NULL,
                    University_ID CHAR (17) UNIQUE,
                    Name VARCHAR (100),
                    Registration_Date DATE, 
                    Catergory CHAR(3) NOT NULL CHECK (Catergory IN ('ACS','NAS','LIB','GST')),
                    Renewed_Date DATE, 
                    PRIMARY KEY (Member_ID));

CREATE TABLE BOOK (ISBN CHAR(10) NOT NULL,
                    TITLE VARCHAR(100),
                    AUTHOR VARCHAR(100),
                    PUBLICATION VARCHAR(100),
                    PUBLICATION_DATE DATETIME,
                    NUMBER_OF_COPY DECIMAL,
                    PRIMARY KEY (ISBN));

CREATE TABLE COPIES (ACCESS_NO CHAR(6),
                    ISBN CHAR(10) NOT NULL,
                    RECEIVED_DATE DATE,
                    AVAILABILITTY BOOLEAN NOT NULL DEFAULT 1,
                    PRIMARY KEY (ACCESS_NO),
                    FOREIGN KEY(ISBN) REFERENCES BOOK(ISBN) ON UPDATE CASCADE);

CREATE TABLE RESERVED_COPY(Member_ID CHAR (10) NOT NULL,
                           ISBN CHAR(10) NOT NULL, 
                           PRIMARY KEY (Member_ID,ISBN),
                           FOREIGN KEY(ISBN) REFERENCES BOOK(ISBN) 
                              ON UPDATE CASCADE,
                           FOREIGN KEY(member_id) REFERENCES member(member_id) 
                              ON UPDATE CASCADE);

asdfg · #4 03-Mar-2008, 23:55

Dear Turbo,

Thakn you very much, My problem is,

FOREIGN KEY(member_id) REFERENCES member(member_id)
ON UPDATE CASCADE);

In this If I want to be set both UPDATE and DELETE constrains?

asdfg · #5 04-Mar-2008, 02:45

Dear Turbo

These are the Error Coming while executes the SQL Commands.

Attached PDF

TurboPT · #6 04-Mar-2008, 05:28

Alright, where's your silent 'strip-spaces' command? (just kidding)...

1. Member, seems to missing the final ')' to create table, plus a few fields, as it appears on the PDF, don't have a space between the column name and the type.

2. Book, same spacing issue for the ISBN column.

3. Copies, fixing #2 should resolve this, but check the spaces after the column names, again.

4. Reserved_copy, look at the error message, what's with the ampersand [&]? Also, check column name spacing.

5. Borrowed_copy, appears to have a stray comma (error hints at location) in the first foreign key declaration, check column spaces too.

asdfg · #7 08-Mar-2008, 04:13

Hi Turbo,

Thank you Very Much for your help on the above problem, I got the solution for that.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
getting an error while compiling and running using different IDE.	jaro	C Programming Language	0	25-Aug-2006 09:14
functions seems not to exit from a certain code block	jaro	C Programming Language	3	21-Mar-2006 23:08
Microsoft SQL Server :: SQL Server message 241, state 1, severity 16	lyuboe	MySQL / PHP Forum	0	11-May-2005 04:31
Compiling error when doing SQL database in C++	confused_pig	CPP / C++ Forum	2	12-Jan-2005 21:08
SQL statement	skyloon	MySQL / PHP Forum	2	12-Oct-2003 06:48

I need help on My SQL Database Creation Statement

Re: I need help on My SQL Database Creation Statement

Re: I need help on My SQL Database Creation Statement

Re: I need help on My SQL Database Creation Statement

Re: I need help on My SQL Database Creation Statement

Re: I need help on My SQL Database Creation Statement

Re: I need help on My SQL Database Creation Statement