CREATE TABLE

[Jeremiah Jacks]

Help! I am totally stumped.
Why won't this execute successfully?

CREATE TABLE user_shipping_info (
user_id INT UNSIGNED NOT NULL,
as_acct VARCHAR(8) NOT NULL,
email_address VARCHAR(40),
company VARCHAR(50),
phone_number VARCHAR(15),
fax_number VARCHAR(15),
address_1 VARCHAR(40),
address_2 VARCHAR(40),
address_3 VARCHAR(40),
address_4 VARCHAR(40),
city VARCHAR(25),
state_abbreviation CHAR(2),
zip VARCHAR(12),
country VARCHAR(25),
UNIQUE INDEX idx_uniq (user_id, as_acct),
FOREIGN KEY (user_id) REFERENCES `user`(id) ON DELETE CASCADE ON
UPDATE CASCADE,
INDEX idx_state_abbreviation (state_abbreviation),
FOREIGN KEY (state_abbreviation) REFERENCES
state(state_abbreviation) ON DELETE SET NULL ON UPDATE CASCADE
) TYPE=INNODB;

Error:
ERROR 1005: Can't create table './tamiyausa/user_shipping_info.frm' (errno:
150)

Thanks in advance!
Jer


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Basic Database Design Question

[Jeremiah Jacks]

You could do it many different ways. Here's one that I would consider:
Create a table to store the business sections (buss_section) and a table to
relate the apps with the business sections (app_buss_section). The design
would abstract the app info from its usage and create a manageable table of
business sections which could be easily modified. Something like this should
work...

Table nms_apps (..., id)
Table buss_section (..., id)
Table app_buss_section (nms_apps.id, buss_section.id)

-Jeremiah

-Original Message-
From: James Walters [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 29, 2003 9:42 AM
To: [EMAIL PROTECTED]
Subject: Basic Database Design Question


Hello,

DB novice checking in here with a basic design
question.  I have a table called 'nms_apps' which
stores information about all of our applications which
we have developed/maintained for our client.  One
column which I would like to use is called 'used_by',
which would store information about which business
sections (Financial Management Branch, Human Resources
Branch, etc.) use a particular application.  Often
times more than one section use a particular
application.  My question is this:

How do you handle multiple entries in one column?  Is
it acceptable to more than one value in a column for
one row?  If not, what is the best way to design that
in the table?  Have multiple 'used_by' columns? 
'used_by_1', 'used_by_2', 'used_by_3', etc.?

Thanks in advance,

Testudo

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.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]

Error with foreign key constraint when updating

[Jeremiah Jacks]

I just upgraded to MySQL 4.0.14-standard for RedHat Linux and am using the
pre-compiled binaries.

I have a database with INNODB tables.
When I insert a row into one of the child tables, I get the following MySQL
error:

INSERT INTO product_access_level (product_id,access_level_id) VALUES
('10201','2') [nativecode=1216 ** Cannot add or update a child row: a
foreign key constraint fails]

I was not getting this error before with the previous version of
MySQL(3.23.57) that I had installed.
Below is the output of the latest foreign key error from 'SHOW INNODB
STATUS':
I am not sure what the problem is here. In the INNODB STATUS it says that my
product table doesn't exist??
Below the status are my table structures. Any input would be helpful.
Thanks!


LATEST FOREIGN KEY ERROR

030728 13:15:03 Transaction:
TRANSACTION 0 554436, ACTIVE 0 sec, process no 22745, OS thread id 864270
insert
ing, thread declared inside InnoDB 500
1 lock struct(s), heap size 320
MySQL thread id 203, query id 11471 localhost root update
INSERT INTO product_access_level (product_id,access_level_id) VALUES
('10201','2
')
Foreign key constraint fails for table tamiyausa/product_access_level:
,
  CONSTRAINT `0_281` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
ON D
ELETE CASCADE
Trying to add to index PRIMARY tuple:
 0: len 5; hex 3130323031; asc 10201;; 1: len 4; hex 0002; asc ;; 2:
len
 6; hex 000875c4; asc u.;; 3: len 7; hex 68338b; asc
h3.;;
But the parent table mydb/product does not currently exist!

-


CREATE TABLE `product_access_level` (
`product_id` varchar(10) NOT NULL default '',
`access_level_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (`product_id`,`access_level_id`),
KEY `idx_product_id` (`product_id`),
KEY `idx_access_level_id` (`access_level_id`),
CONSTRAINT `0_281` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON
DELETE CASCADE,
CONSTRAINT `0_282` FOREIGN KEY (`access_level_id`) REFERENCES `access_level`
(access_level_id`) ON DELETE CASCADE) TYPE=InnoDB

CREATE TABLE `product` (
`id` varchar(10) NOT NULL default '',
`name` varchar(100) NOT NULL default '',
`category_id` int(10) unsigned default NULL,
`retail_value` float unsigned default NULL,
`dealer_price` float unsigned default NULL,
`minimum_purchase` int(10) unsigned default NULL,
`case_quantity` int(10) unsigned default NULL,
`status_id` char(2) default NULL,
`description` text,
PRIMARY KEY  (`id`),
KEY `idx_category_id` (`category_id`),
KEY `idx_status_id` (`status_id`),
CONSTRAINT `0_274` FOREIGN KEY (`status_id`) REFERENCES `product_status`
(`id`) ON DELETE SET NULL,
CONSTRAINT `0_34` FOREIGN KEY (`category_id`) REFERENCES `category`
(`category_id`) ON DELETE SET NULL) TYPE=InnoDB

CREATE TABLE `access_level` (
`access_level_id` int(10) unsigned NOT NULL default '0',
`access_level_name` varchar(25) NOT NULL default '',
PRIMARY KEY  (`access_level_id`)) TYPE=InnoDB


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]