C:\mysql\bin>mysql -u elmasri -pnavathe company
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29 to server version: 4.1.0-alpha-max-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select database();
+------------+
| database() |
+------------+
| company |
+------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE EMPLOYEE
-> (
-> FNAME VARCHAR(15) NOT NULL,
-> MINIT CHAR,
-> LNAME VARCHAR(15) NOT NULL,
-> SSN CHAR(9) NOT NULL,
-> BDATE DATE,
-> ADDRESS VARCHAR(30),
-> SEX CHAR,
-> SALARY DECIMAL(10,2),
-> SUPERSSN CHAR(9),
-> DNO INT NOT NULL DEFAULT 1,
->
-> PRIMARY KEY (SSN),
-> INDEX (SUPERSSN),
-> INDEX (DNO)
->
-> )TYPE = INNODB;
Query OK, 0 rows affected (0.00 sec)
mysql> DESCRIBE EMPLOYEE\G
*************************** 1. row ***************************
Field: FNAME
Type: varchar(15)
Collation: latin1_swedish_ci
Null:
Key:
Default:
Extra:
*************************** 2. row ***************************
Field: MINIT
Type: char(1)
Collation: latin1_swedish_ci
Null: YES
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: LNAME
Type: varchar(15)
Collation: latin1_swedish_ci
Null:
Key:
Default:
Extra:
*************************** 4. row ***************************
Field: SSN
Type: varchar(9)
Collation: latin1_swedish_ci
Null:
Key: PRI
Default:
Extra:
*************************** 5. row ***************************
Field: BDATE
Type: date
Collation: latin1_swedish_ci
Null: YES
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Field: ADDRESS
Type: varchar(30)
Collation: latin1_swedish_ci
Null: YES
Key:
Default: NULL
Extra:
*************************** 7. row ***************************
Field: SEX
Type: char(1)
Collation: latin1_swedish_ci
Null: YES
Key:
Default: NULL
Extra:
*************************** 8. row ***************************
Field: SALARY
Type: decimal(10,2)
Collation: binary
Null: YES
Key:
Default: NULL
Extra:
*************************** 9. row ***************************
Field: SUPERSSN
Type: varchar(9)
Collation: latin1_swedish_ci
Null: YES
Key: MUL
Default: NULL
Extra:
*************************** 10. row ***************************
Field: DNO
Type: int(11)
Collation: binary
Null:
Key: MUL
Default: 1
Extra:
10 rows in set (0.00 sec)
mysql> DROP TABLE IF EXISTS DEPARTMENT;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE DEPARTMENT
-> (
-> DNAME VARCHAR(15) NOT NULL,
-> DNUMBER INT NOT NULL,
-> MGRSSN CHAR(9) NOT NULL DEFAULT '888665555',
-> MGRSTARTDATE DATE,
->
->
-> PRIMARY KEY (DNUMBER),
-> UNIQUE (DNAME),
-> INDEX (MGRSSN)
-> )TYPE = INNODB;
Query OK, 0 rows affected (0.02 sec)
mysql> describe department\G
*************************** 1. row ***************************
Field: DNAME
Type: varchar(15)
Collation: latin1_swedish_ci
Null:
Key: UNI
Default:
Extra:
*************************** 2. row ***************************
Field: DNUMBER
Type: int(11)
Collation: binary
Null:
Key: PRI
Default: 0
Extra:
*************************** 3. row ***************************
Field: MGRSSN
Type: varchar(9)
Collation: latin1_swedish_ci
Null:
Key: MUL
Default: 888665555
Extra:
*************************** 4. row ***************************
Field: MGRSTARTDATE
Type: date
Collation: latin1_swedish_ci
Null: YES
Key:
Default: NULL
Extra:
4 rows in set (0.00 sec)
mysql>
mysql> # from now on the coding causes trouble
mysql>
mysql> ALTER TABLE EMPLOYEE
-> FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
-> ON DELETE SET NULL
-> ON UPDATE CASCADE;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresp
onds to your MySQL server version for the right syntax to use near
'FOREIGN KEY
(SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET
mysql>
mysql> ALTER TABLE EMPLOYEE
-> FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
-> ON DELETE SET DEFAULT
-> ON UPDATE CASCADE;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresp
onds to your MySQL server version for the right syntax to use near
'FOREIGN KEY
(DNO) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE S
mysql>
mysql> ALTER TABLE DEPARTMENT
-> FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
-> ON DELETE SET DEFAULT
-> ON UPDATE CASCADE ;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresp
onds to your MySQL server version for the right syntax to use near
'FOREIGN KEY
(MGRSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET D
mysql>
mysql>
I try again with
C:\mysql\bin>mysql -u elmasri -pnavathe company < Company_08.sql >
out.txt
ERROR 1064 at line 40: You have an error in your SQL syntax. Check the
manual t
hat corresponds to your MySQL server version for the right syntax to use
near 'F
OREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET
C:\mysql\bin>type Company_08.sql
USE company;
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE
(
FNAME VARCHAR(15) NOT NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX CHAR,
SALARY DECIMAL(10,2),
SUPERSSN CHAR(9),
DNO INT NOT NULL DEFAULT
1,
PRIMARY KEY (SSN),
INDEX (SUPERSSN),
INDEX (DNO)
)TYPE = INNODB;
DESCRIBE EMPLOYEE;
DROP TABLE IF EXISTS DEPARTMENT;
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL DEFAULT '888665555',
MGRSTARTDATE DATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
INDEX (MGRSSN)
)TYPE = INNODB;
ALTER TABLE EMPLOYEE
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET NULL
ON UPDATE CASCADE;
ALTER TABLE EMPLOYEE
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT
ON UPDATE CASCADE;
ALTER TABLE DEPARTMENT
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET DEFAULT
ON UPDATE CASCADE ;
DESCRIBE DEPARTMENT;
SHOW CREATE TABLE EMPLOYEE;
SHOW CREATE TABLE DEPARTMENT;
SHOW TABLE STATUS FROM company LIKE "EMPLOYEE";
SHOW TABLE STATUS FROM company LIKE "DEPARTMENT";
Can someone please take a look at my coding,
-----Ursprüngliche Nachricht-----
Von: Victoria Reznichenko [mailto:[EMAIL PROTECTED]
Gesendet: Freitag, 8. August 2003 13:02
An: [EMAIL PROTECTED]
Betreff: Re: mutual declarations produce Error 1064
"Morten Gulbrandsen" <[EMAIL PROTECTED]> wrote:
> I think something is wrong with my administration,
>
> Basically I get these messages,
>
> ERROR 1005 at line 9: Can't create table '.\company\department.frm'
> (errno: 150)
>
> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using
password:
> YES)
>
> ERROR 1133: Can't find any matching row in the user table
>
> C:\mysql\examples\elmasri>mysql -u root -p*******
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 16 to server version:
4.1.0-alpha-max-debug
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> use mysql;
> Database changed
> mysql> update user set password = password('navathe') where user =
> 'elmasri';
> Query OK, 1 row affected (0.02 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> flush privileges;
> Query OK, 0 rows affected (0.02 sec)
>
> mysql> quit
> Bye
>
> C:\mysql\examples\elmasri>mysql -u elmasri -pnavathe company
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 17 to server version:
4.1.0-alpha-max-debug
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> quit
> Bye
>
> C:\mysql\examples\elmasri>mysql -u elmasri -pnavathe company <
> company_01.sql
> ERROR 1005 at line 9: Can't create table '.\company\department.frm'
> (errno: 150)
>
>
> C:\mysql\examples\elmasri>
> C:\mysql\examples\elmasri>type company_01.sql
> # mysql -u root -h localhost -p -vvv < company_01.sql
>
> USE company;
>
> # SHOW INNODB STATUS;
>
> DROP TABLE IF EXISTS DEPARTMENT;
>
> CREATE TABLE DEPARTMENT
> (
> DNAME VARCHAR(15) NOT NULL,
> DNUMBER INT NOT NULL,
> MGRSSN CHAR(9) NOT NULL,
> MGRSTARTDATE DATE,
>
> PRIMARY KEY (DNUMBER),
> UNIQUE (DNAME),
>
> INDEX (MGRSSN), # between employee and department
> FOREIGN KEY (MGRSSN) # a sort of mutually declaration
> REFERENCES EMPLOYEE(SSN) # this is declared in employee
>
> )TYPE = INNODB;
You create table DEPARTMENT with foreign key, which is referenced to the
non-existent table EMPLOYEE. So, you need to create both table without
foreign keys and then add foreign key constraints or execute SET
FOREIGN_KEY_CHECKS=0 before table creation.
===
I tried to add foreign key constrains after
Both tables was created,
I believe my code is pure sql, but not yet implemented in mysql
I'd like to distribute the foreign keys according to software
specification,
Yours Sincerely
Morten Gulbrandsen
===
>
>
> DROP TABLE IF EXISTS EMPLOYEE;
>
> CREATE TABLE EMPLOYEE
> (
> FNAME VARCHAR(15) NOT NULL,
> MINIT CHAR,
> LNAME VARCHAR(15) NOT NULL,
> SSN CHAR(9) NOT NULL,
> BDATE DATE,
> ADDRESS VARCHAR(30),
> SEX CHAR,
> SALARY DECIMAL(10,2),
> SUPERSSN CHAR(9),
> DNO INT NOT NULL,
>
> PRIMARY KEY (SSN),
> INDEX (SUPERSSN, SSN),
>
> FOREIGN KEY (SUPERSSN)
> REFERENCES EMPLOYEE(SSN),
>
> INDEX (DNO, DNUMBER),
> FOREIGN KEY (DNO)
> REFERENCES DEPARTMENT(DNUMBER) # this is declared in department
> )TYPE = INNODB;
>
>
> C:\mysql\examples\elmasri>mysql -u root -p*******
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 19 to server version:
4.1.0-alpha-max-debug
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> grant all on company.* to 'elmasri'@'localhost' identified by
> 'navathe';
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> quit
> Bye
>
> C:\mysql\examples\elmasri>mysql -u elmasri -pnavathe company;
> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using
password:
> YES)
>
> C:\mysql\examples\elmasri>mysql -u root -p*******
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 21 to server version:
4.1.0-alpha-max-debug
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> set password for 'elmasri'@'localhost' = password('navathe');
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> set password for 'elmasri'@'%' = password('navathe');
> ERROR 1133: Can't find any matching row in the user table
> mysql>
It means the there is no any entry 'elmasri'@'%' in the table user.
> I try again with
> Manually
>
> mysql> create table employee( fname varchar(15) )type=innodb;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> DROP TABLE IF EXISTS EMPLOYEE;
> Query OK, 0 rows affected (0.00 sec)
>
> And
>
>
> mysql> CREATE TABLE EMPLOYEE
> -> (
> -> FNAME VARCHAR(15) NOT NULL,
> -> MINIT CHAR,
> -> LNAME VARCHAR(15) NOT NULL,
> -> SSN CHAR(9) NOT NULL,
> -> BDATE DATE,
> -> ADDRESS VARCHAR(30),
> -> SEX CHAR,
> -> SALARY DECIMAL(10,2),
> -> SUPERSSN CHAR(9),
> -> DNO INT NOT NULL,
> ->
> -> PRIMARY KEY (SSN),
> -> INDEX (SUPERSSN, SSN),
> ->
> -> FOREIGN KEY (SUPERSSN)
> -> REFERENCES EMPLOYEE(SSN),
> ->
> -> INDEX (DNO, DNUMBER),
> -> FOREIGN KEY (DNO)
> -> REFERENCES DEPARTMENT(DNUMBER) # this is declared in department
> -> )TYPE = INNODB;
> ERROR 1072: Key column 'DNUMBER' doesn't exist in table
I don't see column DNUMBER in the above create table definition.
> mysql> create table employee( fname varchar(15) )type=innodb;
> Query OK, 0 rows affected (0.00 sec)
>
>
>
> mysql>
> mysql> CREATE TABLE EMPLOYEE
> -> (
> -> FNAME VARCHAR(15) NOT NULL,
> -> MINIT CHAR,
> -> LNAME VARCHAR(15) NOT NULL,
> -> SSN CHAR(9) NOT NULL,
> -> BDATE DATE,
> -> ADDRESS VARCHAR(30),
> -> SEX CHAR,
> -> SALARY DECIMAL(10,2),
> -> SUPERSSN CHAR(9),
> -> DNO INT NOT NULL,
> ->
> -> PRIMARY KEY (SSN),
> -> INDEX (SUPERSSN, SSN),
> ->
> -> FOREIGN KEY (SUPERSSN)
> -> REFERENCES EMPLOYEE(SSN),
> ->
> -> INDEX (DNO),
> -> FOREIGN KEY (DNO)
> -> REFERENCES DEPARTMENT(DNUMBER) # this is declared in department
> -> )TYPE = INNODB;
> ERROR 1005: Can't create table '.\company\employee.frm' (errnoo: 150)
> mysql> exit
> Bye
>
> C:\mysql\examples\elmasri>mysql -u elmasri -pnavathe company <
> company_01.sql
> ERROR 1005 at line 9: Can't create table '.\company\department.frm'
> (errno: 150)
>
> This shows that I actually can create a table,
>
> My question is now,
>
> Is this code ansi sql correct ?
>
> How can I Modify it to run under the latest MySQL ?
>
> Is it something wrong with the users I have created ?
>
> I expect one user and one password for entering mysql,
> And depending upon which database I'd like to use
> I expect for each database I'd like to use, one second password,
>
> This seems to be another topic,
> When I write
>
> mysql> grant all on company.* to 'elmasri'@'localhost' identified by
> 'navathe';
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> grant all on company.* to 'elmasri'@'%' identified by
'navathe';
> Query OK, 0 rows affected (0.00 sec)
>
> mysql>
>
> Then I should be able to do whatever SQL permits ?
User 'elmasri' has all privileges on the database 'company'.
--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
