Associative Table Or A Directly Foreign Key
I am working with hibernate and i want to map my Objects to tables structre.
There is two ways to map relation:
1. with associative table
2. directly foreign key (i mean without associative table)
Can you please explain what are the advantages for each way?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
How To Insert Images Directly Into Mysql Table
Im working on a page for photographer. And he wants some amount of photos to have there and change it whenever he would like to. I thought i put all the photos in mysql db and i would pull it out to webpage using variables. But Im stuck already in the first step - STORING THOSE IMAGES INSIDE A TABLE OF DB. I read about BLOB and avr_row_length and max_rows, but nothing is working. The image is stored in many rows and the output is just strange symbols. I also read those long, hard-mind taking scripts how to upload image using php. But i dont want to slowstep through php, uploading file etc, I just want something like: LOAD DATA INFILE 'image.img' into table tbl_name; and then using php on my page to display it.
Storing Associative Array In MySQL
Is there a way to store an associative array that's arbitrarily long and deep in MySQL? For example, I'd like to do something like this into a field: array("a" => "b", "c" => array("d" => "e", "f" => "g"), "h" => "i");
Using MyISAM Directly
Has anybody had experience accessing MySQL tables via MyISAM library? I'm working on an application that has to have both SQL-like and DBM-like (file-level) access to the table data. I thought that using MyISAM directly would be a good idea. If anybody has any better suggestions, I am all ears. Also, I was wondering if it's possible to use Berkeley DB tools and libraries to access MySQL data files, which are set to use BDB as the storage engine. If it is, can somebody point me at some examples (except what's in the source already).
Using MyISAM Directly
Has anybody had experience accessing MySQL tables via MyISAM library? I'm working on an application that has to have both SQL-like and DBM-like (file-level) access to the table data. I thought that using MyISAM directly would be a good idea. If anybody has any better suggestions, I am all ears. Also, I was wondering if it's possible to use Berkeley DB tools and libraries to access MySQL data files, which are set to use BDB as the storage engine. If it is, can somebody point me at some examples (except what's in the source already).
Update Value Directly In The Same Query
Is it possible to update a value directly, adding text to it, without getting it first, adding the text in php and then do the update. for example: value in field users: ",45," and then adding ,34, to the field directly by doing something like: update table set users='users,34,' so the value in the field afterwards will be ",45,,34,"?
Joining Tables Which Aren't Directly Related
I'm struggling to get my head round a SQL query and wonder if you might be able to help (I'm using MySQL version 4.1.20-1). I have three tables: accounts, items and invoices. CREATE TABLE accounts ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, ... PRIMARY KEY (id) ) ENGINE = InnoDB; CREATE TABLE items ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, accounts_id INT UNSIGNED, ... PRIMARY KEY (id), FOREIGN KEY (accounts_id) REFERENCES accounts (id) ON DELETE SET NULL ) ENGINE = InnoDB; CREATE TABLE invoices ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, items_id INT UNSIGNED, ... PRIMARY KEY (id), FOREIGN KEY (items_id) REFERENCES items (id) ON DELETE CASCADE ) ENGINE = InnoDB; Each account can have multiple items and each item can have multiple invoices. Hope you're with me so far? So here's the question. If I have an account ID, how do I get a list of invoice IDs for that account when accounts and invoices aren't directly related?
Inserting Small Image Directly Into Database
How do you insert an image into a table directly using something like data studio? I have tried googling it and it seems all the examples are using a language to do it for you. The images are between 2-4k and the table is just an int column followed by a blob column. I just thought the statement would go like INSERT INTO sometable VALUES (1,'image.jpg'); The web suggests uploading and downloading in PHP (which im using) but im building the data first using data studio then just displaying the table information using PHP. Before i go into displaying the information on the page i just wanted to make sure the insert query is correct.
Connect To Sqlserver Directly From Unix Prompt
How can I connect directly to sqlserver from a Unix Prompt? I am using DataDirect in the ODBC connectivity. Is there any way whether I can connect to SQLServer from the prompt,just a select stmt will do. For eg: if I use select * from table|sqlplus -s usernmae/password@hostname
MySQL Administrator Backup Directly To Local Database
In MySQL administrator is there a way to backup directly to my local database from a remote host. I would ideally have this done automatically once a day. I like to test locally and don't want to have to restore the database locally each time a backing from the remote site is done.
How To Connect Directly To Mysql Server Port (3306) Using TCP/IP Protocol
I want to develop an application in LabWindows/CVI (ANSI C developpment software for instrumentation) from which I could connect directly to mysql server port (3306) using TCP/IP protocol. This developpment environment allows me to use functions to connect, read and write to a certain port using TCP/IP protocol. I must say I'm new to using MySQL and I searched the manual without any response to my question.
SUM Of Two Foreign Table Fields
I've a project that has many timesheets (time) and many expenses (money) I'm trying to work out the cost of the project base on a set amount per hour for timesheets plus expenses. A project may not always have expenses. A project has zero or many timesheets A project has zero or many expenses I need an SQL statement to return total time for a project and total expenses so i can do my calculations then in php. I'd prefer do this in one SQL statement. What i have so far: SELECT project.name, SUM(timesheet.time) AS time, SUM(expenses.amount) AS expense FROM project, timesheet, expenses WHERE project.id = timesheet.project_id AND project.id = expenses.project_id GROUP by project.id ==TABLES== PROJECT id PK name TIMESHEET id PK project_id FK time EXPENSES id PK project_id FK amount This will work fine for all projects that have both at least one expense and one timesheet. But if they are missing one then it returns no row for that project and therefore i can't display the cost. There are other pieces of data but these are the only important ones. I can do it in two different SQL statements but i really think there has to be a way to do it in one.
Altering My Table To Add Foreign Key
i have two tables, cars and people. cars: make model engine people: name address sex what i would like to do is alter my table to create a foreign key 'MAKE' in the people table. i dont know the syntax to do this.
Which Table Is Foreign Key Constraint On
When trying to delete a record from a table, I receive the message "[root@localhost:3306] ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails". I know there is a foreign key, but don't know what table it is on. Is there an easy way to find out what table the foreign key is in that is restricting the record from being deleted.
INSERT ID From Foreign Key Table
I have a form used to populate 2 tables: Provider and Contacts. Provider has a foriegn key column - Contact1ID - which has a foreign key relationship with the prmary key - ID - in the Contacts table. The form submits data to Contacts then Provider but I need the ID generated by the Contacts submission to be entered into the Contact1ID column in the Provider table. If I use this: INSERT INTO Provider(Contact1ID) SELECT ID FROM Contacts WHERE FirstName='$contact1{FirstName}' AND Surname='$contact1{Surname}' .... all I get is a new row. UPDATE doesn't seem to allow SELECT to grab the new ID either. Any ideas?
How To Make One Table Has Two Foreign Keys
i just start to use mysql and i want to know how to write the command to make two fields as a foreign keys For Example, table 1 school_code (PK) School_room Bldg_code Office_room (FK) Prof_id (FK) how can i write this command using constraint?
Cannot Modify Table To Create A New Foreign Key
I used this syntax: ALTER TABLE table1 CHANGE field1 field1 char(60) NOT NULL REFERENCES table2(field2) field1 is INDEX for table1 field2 is PRIMARY KEY for table2 table1 and table are InnoDB type I'm getting this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCES table2(field2)' at line 1 What's the problem ? I followed MySQL Reference Manual to create the query, it' supposed to work.
Adding A Foreign Key Into An Existing Table
May I know how should I do that? The SQL statement should be: CREATE TABLE USER_SYSTEM_SECURITY ( USERID VARCHAR(32), HOSTID VARCHAR(32), HOME_DIRECTORY VARCHAR(32) NOT NULL, ACCESS_RIGHTS INT(3) NOT NULL, PASSWORD_EXPIRY_DATE DATE, ROLES VARCHAR(32) NOT NULL, PRIMARY KEY (USERID, HOSTID), FOREIGN KEY (USERID) REFERENCES MEMBER (USERID) ON DELETE CASCADE, FOREIGN KEY (HOSTID) REFERENCES SYSTEM (HOSTID) ON DELETE CASCADE); But I forgot to add in the last row during the table creation.
Foreign Key Relations In MyISAM Table
I am trying to setup a database with 2 tables 'Items' and 'Categories'. There descriptions are: Items(itemID,itemCategory,itemDescription) Categories(Category) I have added each of the databases, but have tried using the following to create a Foreign Key Relationship, but this doesn't appear to work. --- ALTER TABLE Items add CONSTRAINT fk_categories FOREIGN KEY (itemCategory) REFERENCES Categories(Category) ON DELETE CASCADE ON UPDATE CASCADE; --- Im running on phpMyAdmin 2.3.2 and MySQL 3.23.58. Both Tables are MyISAM, i have read abit about INNODB but my host doens't give the option for that database type. The only other options are Heap, BerkleyDB, ISAM and Merge. Im wondering if anyone can help me to work out how to create the relationship and where i can view it. After using the above, it accepts the code in the SQL window, but when a Category is changed in Categories, the Items table does not update.
Multi Field In Same Table Reference To Same Foreign Key
I am having trouble to come up with correct query, could someone give me a hand? I have created a table which contains several fields (SampleUser_ID, TestingUser_ID, Report_User_ID) and all of these fields reference to same User Talbe's primary key - user-ID. It works fine when I retrieve data only with Sample person name (as following) SELECT Resulttable.ID, Resulttable.data, lk_operators.UserName as 'SampleName' FROM lk_operators RIGHT JOIN ResulttableON lk_operators.User_ID = Resulttable.SampleUser_Id But I having problem to display all officer's name who work on the different task for the same sample. eg: Result.ID, SamplePerson_name, Testing_person_name, Report_person_name, Result.data I have try join on top of join without any luck. Can someone point me to the right direction?
Can't Add Foreign Key Contraint While Referencing Table Has Contents
using MySQL administrator to add foreign key constraint as such: ALTER TABLE `testhibernate`.`city` ADD CONSTRAINT `FK_countryid123123` FOREIGN KEY `FK_countryid123123` (`countryId`) REFERENCES `countries` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT; when the country-table contains data this gives: "MySQL Error number 1005 Can't create table ... (errno: 150)" when I delete the data from country-table the command is executed succesfully. Am I correct in thinking that it's not possible to lay contraints on tables while they contain data (in this way)?
Mutli Table INSERT With Foreign Keys
I want to add a new row to one table and then add an other row to a second table where one column is a foreign key to the first table. The first table´s primary key is set to auto increasment and I wounder if there is a simple way to find out the primary key´s "auto-set-value" from the first INSERT so I can use it when I fire the second INSERT? example: INSERT INTO tableA VALUES('','Mike','Male') INSERT INTO tableB VALUES('','Boston','?AboveInsertPrimeKey?')
Table Design, Normalization, Foreign Keys
In building a MySQL database what are the best practices to optimize speed and normalize? This is a food management database. Tables are such as item, manufacturer, brand, vendor, store location, nutrition facts, price, store dept, packaging, item type. And a few others. Given: 1. Tables are concise and have keys on all relevant search fields. 2. All tables have a prime, integer id field. (First field in table.) 3. Any repeating data field will be normalized. (Except for a few very minor areas.) Questions: 1. Use where field=field clauses to link the various tables only or mostly? 2. Use joins? 3. Use foreign field requirements to insure links and data completion? 4. Use triggers & procedures to do (3). 5. Have application programmers enforce the business logic? (Not really favored.) 6. Which approach is fastest? Where clauses to join, join clauses? Does it matter? Any recommended design tools? Am using MySQL Browser & Administrator.
MySQL Table Design Style With Foreign Keys
I just wondering what proper (My)SQL style would be for the following situation (which I made up for the sake of this)... You have a SpiceRack table and a Spice table. Each rack has many spices, in typical one-to-many fashion. So the Spice table would have rackID column. Easy. But let's say each rack has one default spice. So should the SpiceRack table have a column "DefaultSpiceID" or should the Spice table have a column "isDefault"? In one sense, I would say the former, to help enforce a single default spice. In another sense, I would say having two tables point to each other is ugly and just do some extra queries to enforce that there is only one with the latter. And while I'm at it... for that isDefault, and similar T/F columns... What's the best data type? SMALLINT (1) UNSIGNED, ENUM(0,1), or LONGBLOB?
Why Use A Foreign Key?
I am going to have a database that will have around 7 tables. Each table will have a UserID column that will be used as the Foreign Key (kind of). I will setup a primary key comprised of the UserID and an Auto Incremented field in each of the tables, and I plan on setting up indexing on the UserID field, all tables but 1 table can and will have duplicates of the UserID, it is how I am implemented my single to many infrastructure. I will be updating and inserting into all the tables at the same time. Some of my queries however may or may not use more than 1 table. Anyways my question is should I specifically setup a Foreign Key? As I stated above I plan on using the UserID field in each table as a Foreign Key....rather a linking identifier, but should I actualy create a Foreign Key?
Foreign Key
I add foreign key to my tables, but mysql seem to don't consider them. For exemple the table BIERE (beer in english) : CREATE TABLE BIERE ( NOM VARCHAR(20) NOT NULL, TYPEB VARCHAR(20) NOT NULL, ORIGINE VARCHAR(20), COTE VARCHAR(10) NOT NULL, PRIMARY KEY(NOM), FOREIGN KEY (TYPEB) REFERENCES TYPEBIERE (NOM) on delete cascade, FOREIGN KEY (ORIGINE) REFERENCES ORIGINE (NOM) on delete cascade, FOREIGN KEY (COTE) REFERENCES COTE (NOM) on delete cascade ); So the foreign key typeb come from the table TYPEBIERE (white...brown..etc) But if I do an insert of a beer and I type "Love" for a type of beer (Love isn't in TYPEBIERE) MySql don't show any error and add this beer to the table?
Foreign Key
I think I've finally figured out how to make a foeign key - you just copy some code into the "SQL query" box near the bottom of a table's Structure view page, right? I thought I also had the right code to paste in, but it isn't working. Here are the stats:
Foreign Key
I've got a strange foreign key constraint problem in that when I try to do an insert into a table with some foreign keys, even though those keys reference valid fields in valid tables, I get the "cannot add or update...." [1216] error message. Code:
Foreign Key
i am able to do good with single db or table with all the queries. I like to learn how to work with multiple tables/dbs, particularly i what to learn seting foreign key?
Foreign Key
I try to create tables with foreign keys so that not to delete rows when linked to another row of another tables i try for example: DROP TABLE IF EXISTS LIVRE; CREATE TABLE LIVRE ( id_livre int(11) NOT NULL auto_increment, id_genre int(11) NULL ,INDEX (id_genre), CONSTRAINT `FK_LIVRE_GENRE` FOREIGN KEY (id_genre) REFERENCES genre(id_genre) ON DELETE RESTRICT ) TYPE=INNODB; but i can delete a row from the table genre when exist a row in LIVRE that use it Can someone give me the right syntax please ?
Foreign Key?
What problems would be there if I don't use foreign keys in stars_in_movies table when I select data? I feel it doesn't really matter. Anyone would guess future problems if I get rid of foreign keys from the stars_in_movies table? movies id:integer (primary key) title:varchar(100) year:integer director:varchar(100) banner_url:varchar(200) trailer_url:varchar(200) stars id:integer (primary key) first_name:varchar(50) last_name:varchar(50) dob:date photo_url:varchar(200) stars_in_movies star_id:integer, foriegn key referencing stars.id movie_id:integer, foreign key referencing movies.id
Foreign Key
I have made 2 tables in sql, these are the table names/fields: product: product_no,name,price,stock user: username,password I now have to create another table called basket, with these 3 fields: username, itemno, quantity. username and itemno are foreign keys to the 'user' and 'product' table, respectively. So, my problem is how to create the this table with this foreign key thing, would it be: CREATE table basket(username VARCHAR(20), itemno INT(4), quantity INt(4)) Thats a simple create table line, but what do i change in it to take into account the foreign key thing?
Foreign Key
can i use two foreign key constraints in one table. i.e can one table refer to two or more tables
Foreign Key
I'd like to create a table with PRIMARY KEY k (a,b,c) where a, b, c are foreign key. Is it possible? How can I do?
Foreign Key
problem: I´ve a table "A" with primary key (a1, a2), and a table "B" where the PK of A is propaged to as follow: (b1,b2) --> A(a1,a2) With this I get an error in MySql front. I also tried with a sql sentence and I get an error again. Maybe Mysql does not allow foreign keys of more than one field?
Foreign Key
i want to link PRIMARY KEY in one table as a FOREIGN KEY in an other. I have applied "InnoDB" to both, and applying the relationship via phpmyAdmin. When i try to enter data into the data base via some php. The Data doesn't seem to be arrive. Even thought the PHP returns say that it has been sent. If I take out the foreign key relationship, the data is sent to the tables.
Add A Foreign Key
The SQL statements below works fine if the line for the foreign key is omitted. With the line included, I get this error message: #1005 - Can't create table './marywhar_VegDb/test_fk.frm' (errno: 150) I've also tried removing the constraint name and ON UPDATE and ON DELETE parameters - no difference. Would someone please tell me what I'm doing wrong? CREATE TABLE `levels` ( `id` tinyint(4) NOT NULL auto_increment, `level` char(16) NOT NULL default '', PRIMARY KEY (`id`) ) TYPE=InnoDB AUTO_INCREMENT=5; INSERT INTO `levels` VALUES (1, 'not specified'); INSERT INTO `levels` VALUES (2, 'low'); INSERT INTO `levels` VALUES (3, 'medium'); INSERT INTO `levels` VALUES (4, 'high'); CREATE TABLE `test_fk` ( `id` smallint(6) NOT NULL auto_increment, `level` tinyint(4) default 1, PRIMARY KEY (`id`), CONSTRAINT `k_level` FOREIGN KEY (`level`) REFERENCES `levels`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT ) TYPE=InnoDB AUTO_INCREMENT=1;
Foreign Key
If I have 3 tables (for example Tables A, B and C) and the Primary Key for table A is a Foreign Key in table B, Do I need to show the Primary Keys of tables A AND B as Foreign Keys in table C if I wish to use data from Tables A and B, OR does the fact that the Primary key of Table A is a Foreign key in Table B mean that I can obtain the data from Table A via Table B (because of the Foreign Key) through the use of a query.
Foreign Key
I tried to create a new table with a foreign key but It doesnt work.. this is the code> CREATE TABLE Groupe ( id_groupe VARCHAR(5) , nbre_member INT UNSIGNED , PRIMARY KEY (id_groupe) ); //the groupe table is created CREATE TABLE Student ( code_student VARCHAR(10), Name VARCHAR(10), id_groupe VARCHAR(5), PRIMARY KEY (code_student) FOREIGN KEY (id_groupe) REFERENCES Groupe(id_groupe) ON DELETE CASCADE ON UPDATE CASCADE ); // error in SQL syntax.
Foreign Key
I have a customer table and a products table. The clientID is a foreign key inside the products table. If i edit the clientId inside the product table i get: 1451 - cannot delete or update a parent row; a foreign key constraint fails ('database01/customers', CONSTRAINT 'client_ibfk_1 'FOREIGN KEY('clientId') REFERENCES 'Products' ('clientId')) any ideas ? :| From the customers table : CONSTRAINT `client_ibfk_1` FOREIGN KEY (`clientId`) REFERENCES 'Products`
|
TRACKER
