dezign generated database .sql file into mysql

[TerryJagger]

I am new at the database game and am very keen to learn (soooo much to learn it appears). I have used the Dezign case tool and made a .sql file that I want to use in MySQL to create a database. I have tried a few ways to load this through the prompt but have had no luck.
If anyone has ideas I am interested.

 

[QatQat]

Can you post the script that has been generated. I am not familiar with Dezign but if I look at the script I can tell you what is wrong.

Bye


Qatqat

 

[TerryJagger]

This is the code generated by Dezign. It looks (to me ) like all you have to do is load it into MySQL as a file but I have no idea what the command at the prompt would be.


Code..............

CREATE TABLE Student(
Student_ID VARCHAR(7) NOT NULL,
Name VARCHAR(30) NOT NULL,
Address VARCHAR(30),
City VARCHAR(20),
Province VARCHAR(2) NOT NULL,
PRIMARY KEY (Student_ID),
UNIQUE UC_Student_ID (Student_ID));


CREATE TABLE Course(
Course_ID DECIMAL(3,3) NOT NULL,
Name VARCHAR(30) NOT NULL,
Program VARCHAR(4) NOT NULL,
Instructor VARCHAR(20),
PRIMARY KEY (Course_ID),
UNIQUE UC_Course_ID (Course_ID));


CREATE TABLE Student_Courses(
Student_ID VARCHAR(7) NOT NULL,
Course_ID DECIMAL(3,3) NOT NULL,
Grade NUMERIC(4,1) NOT NULL,
FOREIGN KEY (Course_ID) REFERENCES Course (Course_ID),
FOREIGN KEY (Student_ID) REFERENCES Student (Student_ID));

 

[QatQat]

In order to insert foreign keys, you should transform your tables into transaction safe tables

example

alter table student type=innodb;

Build your tables manually, it is much better; are you really sure that you want to have a primary key field varchar?

You can download a good reference book from

http://www.mysql.com/Downloads/Manual/manual.pdf-2002-07-26.zip

This is a pdf format

SQL is very easy to learn.

Anyway after you have refined your script save it into a .sql file and do the following at prompt

c:\mysql\bin>mysql -u username -ppassword < path_to_your_script\script.sql

I suggest you read the tutorial on the manual before doing anything.


Bye

Qatqat

 

[TerryJagger]

MySQL prompt comes up without a username and password (I have not gotten to the security part yet).
I assume that when I get to the MySQL prompt ( based on your advice ) that this is what I need:

mysql> path_to_your_script\script.sql


is this correct?

A manual came with the installation and I checked it closely and could not find what I was looking for, but I did download the one you suggested in case it has more info than mine. I am just doing this as an exercise to get my feet wet on this stuff.
I have created a DB manually based on the same info above but just want to see if I can get this to work.

TJ

 

[QatQat]

You have to pass the request to dos prompt and not mysql prompt.

First, if you are using windows version of Mysql, modify you autoexec.bat adding this line

set path = c:\mysql\bin
This will allow you to access mysql by simply typing 'mysql'.

After you have done that, let's import your sql script
Save the script on cpen DOS prompt and type:


mysql < c:\script.sql


where script is the name of the file you have created.

MySQL will give you an error message if your script is not correct but not any confirmation if it is accepted.

After succesfully executing your .sql script you have to log in your server

type 'mysql'

Now you can check what you have just done.

Bye


Qatqat

 

[TerryJagger]

Very basic info!!!
That is exactly what I needed.
I am going to give it a try right now.
Thanks Terry

 

[TerryJagger]

I tried and it works great!!!
Thanks again
Terry

 

[QatQat]

You are welcome

Good Luck with your SQL


Bye

Qatqat