MySQL Forums - Foreign Keys (Referential Integrity)

timestamp is not updated on foreign key update (no replies)

Hemant Shah — Sat, 18 May 2013 00:42:37 +0000

I am using MYSQL 5.1.38 and I have following tables:

create table table1 (
col1 varchar(50) primary key not null,
ts1 timestamp not null default current_timestamp on update current_timestamp
)engine=innodb;

create table table2 (
col1 varchar(50) not null,
ts2 timestamp not null default current_timestamp on update current_timestamp,
foreign key (col1) references table1 (col1) on update cascade on delete cascade
)engine=innodb;

When I update col1 in table1, the ts1 in table1 and col1 in table2 are updated but ts2 in table2 does not get updated.

Here is the output:

mysql> insert into table1 (col1) values ('test');
Query OK, 1 row affected (0.00 sec)

mysql> insert into table2 (col1) values ('test');
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1;
+------+---------------------+
| col1 | ts1 |
+------+---------------------+
| test | 2013-05-17 09:37:56 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> select * from table2;
+------+---------------------+
| col1 | ts2 |
+------+---------------------+
| test | 2013-05-17 09:38:03 |
+------+---------------------+
1 row in set (0.01 sec)

mysql> update table1 set col1='test1' where col1 = 'test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from table1;
+-------+---------------------+
| col1 | ts1 |
+-------+---------------------+
| test1 | 2013-05-17 09:44:28 |
+-------+---------------------+
1 row in set (0.00 sec)

mysql> select * from table2;
+-------+---------------------+
| col1 | ts2 |
+-------+---------------------+
| test1 | 2013-05-17 09:38:03 |
+-------+---------------------+
1 row in set (0.00 sec)

I would expect ts2 to be updated as well. Is this expected behaviour?

How know many to many relationship from information schema (1 reply)

Jorge prado — Sat, 16 Mar 2013 06:34:01 +0000

how to know if a relationship is many to many type from INFORMATION SCHEMA

error 1005 (hy000): can't create table reparto.simone (errno:105) (no replies)

simone mancaruso — Fri, 01 Feb 2013 13:38:33 +0000

why i can't create the table reparto??? i use mysql 5.5 command line client

Create table film (
anno_produzione integer not null,
titolo char not null,
versione char,
fascia_appartenenza char,
primary key (anno_produzione, titolo),
unique (titolo));

create table abbonamento (
cod integer primary key not null,
Tipo char,
Sconto integer,
Data_reg datetime,
Ncopie integer,
Durata char,
unique (cod));

create table regione (
nome char primary key not null,
unique (nome));

create table distributore (
pi integer primary key not null);

create table lingua (
nome char primary key not null);

create table provincia (
nome char primary key not null,
nome_regione char ,
foreign key (nome_regione) references regione (nome),
unique (nome));

create table citta (
nome char primary key not null,
nome_provincia char,
pi_distributore integer,
foreign key (nome_provincia) references provincia (nome),
foreign key (pi_distributore) references distributore (pi),
unique (nome));

create table servicepoint (
pi integer primary key not null,
pi_distributore integer,
nome_citta char,
foreign key (pi_distributore) references distributore (pi),
foreign key (nome_citta) references citta (nome),
unique (pi));

create table copiafisica (
supporto char not null,
titolo_film char not null,
anno_produzione_film integer not null,
primary key (supporto, titolo_film, anno_produzione_film),
foreign key (titolo_film) references film (titolo),
foreign key (anno_produzione_film) references film (anno_produzione));

create table magazzino (
via char not null,
numero_civico integer not null,
pi_servicepoint integer not null,
nome_citta char,
primary key (via, numero_civico, pi_servicepoint),
foreign key (pi_servicepoint) references servicepoint (pi),
foreign key (nome_citta) references citta (nome),
unique (via, numero_civico));

create table reparto (
nome char primary key not null,
numero_civico_magazzino integer,
via_magazzino char,
foreign key (numero_civico_magazzino) references magazzino (numero_civico),
foreign key (via_magazzino) references magazzino (via));

Importing dump from relational db while maintaining referential integrity (no replies)

Fredrik Krokstad — Mon, 28 Jan 2013 17:03:03 +0000

Hi

For the sake of this example. let's say I have a central server that contains 1000s of rows spread over several tables. They're linked together with foreign keys, that is, a relational database (InnoDB). This is database A.

I have several client databases from which I generate partial dumps that needs to be merged into database A. The client databases have the same schemata as database A.

While maintaining referential integrity, how do I import the client dumps into database A? If, let's say, I dump the contents of client databases, the keys and foreign keys are "hardcoded" into the dump-file. If I were to import this into database A, MySQL would just throw "Duplicate entry 'x' for key 'primary'". If I were to exclude id's from the dump-file, referential integrity would not be maintained between the different tables.

Is there some ingenious way of doing this that I'm missing?

Thanks in advance!

Normalise and uniqueness of a dataset where the combination of three columns is unique and one column is a many to many relationship (no replies)

diego gullo — Thu, 06 Dec 2012 11:42:20 +0000

I am faced with a unique requirement and I cannot figure out the elegant way of implementing this without a trigger, but that sounds like a workaround rather than proper design.

Lets say we have this non normalised dataset

Object ID (PK) Server Name domain role priority
1 server1.domain.name ALL 1 P
2 server2.domain.name 1,2,3 2 B
3 server3.domain.name 1,2,3,4,5 1 S
4 server4.domain.name ALL 3 S
5 server5.domain.name 3,4,5 2 S
6 server6.domain.name ALL 2 B
7 server7.domain.name 1,2,3,4,5 1 S DUP
8 server8.domain.name ALL 3 S DUP

Above DUP means duplicate as in this breaks the following constraints

-combination of domain, role and priority is unique (see the red lines break this uniqueness)
-domain column is a list of IDs or ALL is assumed (i implement this with a many to many relationship so if there is none ALL is assumed )

What design would you apply (if you could provide create statements would be great but not necessary) to have the above relations and constraints normalised?

Thanks in advance for replying

The text included in this forum might not show the raw data well. Please check this image to clarify it
http://bizmate.dreamhosters.com/normalise_unique_multipleFields.png

error in foreign key constraint definition (2 replies)

Matteo Tassinari — Fri, 09 Nov 2012 12:34:33 +0000

Hi everybody,

I have a problem creating a foreign key constraint between two tables.

Here is the first table:

CREATE TABLE `agews_rifiuti_cer` (
`id_cer` int(10) unsigned NOT NULL AUTO_INCREMENT,
`agews_id` int(10) unsigned DEFAULT '0',
`livello` tinyint(4) DEFAULT '1',
`codice` varchar(10) DEFAULT NULL,
`descrizione` varchar(255) DEFAULT NULL,
`note` text,
`flag_pericoloso` tinyint(1) DEFAULT '0',
`id_cliente` int(10) unsigned DEFAULT '1',
`flag_modificato` char(1) DEFAULT 'N',
PRIMARY KEY (`id_cer`),
KEY `fk_id_cliente_agews_sgs_codici_cer` (`id_cliente`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And here is the second one:

CREATE TABLE `lin_98_47_rifiuti` (
`id_rifiuto` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_azienda` int(10) unsigned NOT NULL DEFAULT '1',
`id_sede` int(10) unsigned NOT NULL DEFAULT '1',
`revisione_documento` int(10) unsigned NOT NULL DEFAULT '0',
`rifiuto` varchar(255) DEFAULT NULL,
`codice_cer` varchar(10) DEFAULT NULL,
`nome_interno` varchar(255) DEFAULT NULL,
`descrizione` text,
`materie_prime` text,
`contenitore` text,
`deposito` text,
`data_ultima_analisi` date DEFAULT NULL,
`stato_fisico` enum('Solido pulverulento','Solido non pulverulento','Fangoso palabile','Liquido') DEFAULT 'Solido non pulverulento',
`quantita` float(9,1) DEFAULT '0.0',
`unita_misura` enum('Kg','l','mc') DEFAULT 'Kg',
`id_pericolo` int(10) unsigned DEFAULT NULL,
`destino` enum('Recupero','Smaltimento') DEFAULT NULL,
`id_recupero` int(10) unsigned DEFAULT NULL,
`id_smaltimento` int(10) unsigned DEFAULT NULL,
`id_cer` int(10) unsigned DEFAULT NULL,
`immagine` varchar(255) DEFAULT NULL,
`image_type` varchar(20) DEFAULT NULL,
`image_content` mediumblob,
`image_size_x` smallint(5) unsigned DEFAULT '0',
`image_size_y` smallint(5) unsigned DEFAULT '0',
`flag_storico` tinyint(1) DEFAULT '0',
`id_responsabile` int(10) unsigned DEFAULT '0',
`nome_responsabile` varchar(255) DEFAULT '0',
`id_ultima_modifica` int(10) unsigned DEFAULT '0',
`create_log` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id_rifiuto`,`id_azienda`,`id_sede`,`revisione_documento`),
KEY `fk_main_lin_98_47_rifiuti` (`id_azienda`,`id_sede`,`revisione_documento`),
KEY `fk_id_responsabile_lin_98_47_rifiuti` (`id_responsabile`,`id_azienda`,`id_sede`,`revisione_documento`,`nome_responsabile`),
KEY `fk_id_pericolo_lin_98_47_rifiuti` (`id_pericolo`),
KEY `fk_id_recupero_lin_98_47_rifiuti` (`id_recupero`),
KEY `fk_id_smaltimento_lin_98_47_rifiuti` (`id_smaltimento`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The problem happens the I try to do this:

ALTER TABLE lin_98_47_rifiuti ADD CONSTRAINT fk_id_cer_lin_98_47_rifiuti FOREIGN KEY (id_cer) REFERENCES agews_rifiuti_cer(id_cer) ON UPDATE CASCADE ON DELETE CASCADE;

And I get this error:

#1005 - Can't create table 'db_626suite.#sql-71c_13d5' (errno: 150)

While the command SHOW INNODB STATUS says:

Error in foreign key constraint of table db_626suite/#sql-71c_13d5:
FOREIGN KEY (id_cer) REFERENCES agews_rifiuti_cer(id_cer) ON UPDATE CASCADE ON DELETE CASCADE:
Cannot resolve column name close to:
) ON UPDATE CASCADE ON DELETE CASCADE

But the syntax and fields definitions seem correct to me. What am I doing wrong?

foreing key and Partitioned table (no replies)

Pieter Huiberts — Fri, 19 Oct 2012 17:27:32 +0000

I am trying to apply pk-fk to a partitioned table and get an error.

Error Code: 1506: Foreign key clause is not yet supported in conjunction with partitioning

Mysql 5.5.12, engine innodb

Is it true that MySQL is not able to do this? How can one have a descent DB?

Foreign Key on part of a unique index (no replies)

Mr Xax — Fri, 10 Aug 2012 18:41:09 +0000

I got the following tables:

A: key(id) data ...
B: key(id,language) data ...

The id from A should link to the id of B. That works fine until I try to delete a entry in B. Because the key consists of id AND language the table could look like this:

(1, DE) Tür
(1, EN) Door
(1, ME) Ding

I want to delete (1, ME), but the foreign key fails (id is in use in table A!)

The FK should only fail if the last entry with ID isdelete (so there is no entry for (id) in any language...) How can I solve this?

Can a single foreign key point to multiple primary keys? (no replies)

David Thielen — Wed, 25 Jul 2012 14:14:10 +0000

Hi all;

I have a case where the DB is set up with (simplifying) a table User and a table SuperUser. Both have a column Id. Every SuperUser row has a row in the User table with the same Id value (not my design!!!). The SUperUser table does not pull common info from the User table, it duplicates it.

Can/should any foreign key that points to User.Id also have a FK:PK relationship with SuperUser.Id? My take on this is it is at a minimum a very bad idea and that many (most?) DBs can't enforce this relationship/

Am I off base here?

change a PK value and all it's FK value (1 reply)

DIL DIL — Mon, 23 Jul 2012 17:47:36 +0000

i have a table1 where each row has a PK, this PK is used in other tables (table2 to table 32) as an FK.

I want to update table1 row1 PK from e.g. 2 to 3 - I also then want to update the value where it's used as an FK in other tables?

I have 32 other tables to update that reference the PK - how can I do it quickly?

the command i have got so far UPDATE table1 set id=2 where id=3; I have come across the command UPDATE CASCADE but can't get that to work

thanks

I can't point foreign key to date field type (1 reply)

Andro Meda — Mon, 23 Jul 2012 17:44:15 +0000

I have 2 table. It called mEmpl and mOrder.

mEmpl have 9 field, there are: IDEmployee (int), ShortName (varchar), Name (varchar), DBegin (date primary key), DEnd (date primary key), Directory (varchar), Mode (varchar primary key), PartTime (varchar), IDJob (int).

mOrder have 11 field, there are: IDOrder (int), NrOrder (int), OrderDesc (varchar), IDOwner (int), Budget (float), DBegin (date), DEnd (date), Mode (varchar), ArtCost (int), BaseRate (int), BaseItem (int).

When I try to add foreign key at mOrder(DBegin, DEnd, and Mode) which references by mEmpl, it always raise error message: ERROR 1005 (HY000): Can't create table 'spezet.#sql-4d4_1' (errno: 150)

However, if I just add foreign key to DBegin only like this: ALTER TABLE mOrder ADD FOREIGN KEY (DBegin) REFERENCES mEmpl(DBegin);

There's not raising any error! The DBegin is successfully pointing to mEmpl.DBegin primary key.

Anyone can describe to me whats wrong here? Whether MySQL indeed can't point to 2 date field at once? Thanks before for your replies..

Foreign keys and indexes autocreation issue (1 reply)

Dimitrios Iosifidis — Mon, 23 Jul 2012 17:54:36 +0000

I am investigating an issue regarding foreign key and (necessary) indexes creation.
At mysql documentation i have read the comment:
"InnoDB enables you to add a new foreign key constraint to a table by using ALTER TABLE:

ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first."

So, in order to identify the different treatment between create table and alter table syntax i have created these two simple tables:

CREATE TABLE `myDomain1` (
`UUIDKey` char(32) default '',
`AttributeValue` varchar(255) NOT NULL unique,
PRIMARY KEY (`UUIDKey`)
) ENGINE=InnoDB;

CREATE TABLE `myDomain2` (
`UUIDKey` char(32) default '',
`Attribute` varchar(255) NOT NULL,
PRIMARY KEY (`UUIDKey`),
constraint `FkmyDomain1AttributeValue` FOREIGN KEY (`Attribute`)
REFERENCES `myDomain1` (`AttributeValue`) ON DELETE CASCADE
);

The creation of myDomain2 was successfull and resulted at the creation of FkmyDomain1AttributeValue index.

However, triggering the sql command
CREATE INDEX `FkmyDomain1AttributeValue` on `myDomain2`(`Attribute`) ;
also succeeds, that leads to the assumption that no index was found?

As a next step, i have used the following syntax for myDomain2 creation:

CREATE TABLE `myDomain2` (
`UUIDKey` char(32) default '',
`Attribute` varchar(255) default NULL,
PRIMARY KEY (`UUIDKey`)
) ENGINE=InnoDB;

alter table `myDomain2`
add constraint `FkmyDomain1AttributeValue`
FOREIGN KEY (`Attribute`)
REFERENCES `myDomain1` (`AttributeValue`) ON DELETE CASCADE;

Again issuing the
CREATE INDEX `FkmyDomain1AttributeValue` on `myDomain2`(`Attribute`) ;
succeeded.

So, two questions arise:
1) What is the meaning of the comment at the documentation? (When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.)
2) Are explicit calls to index creation needed? This would have a big impact on performance i guess...

Another important remark in terms of this issue is that when experimenting with MySQL v5.5 the test has different behaviour; the create index sql command fails with the following:
create index `FkmyDomain1AttributeValue` on `myDomain2`(`Attribute`) Error Code: 1280. Incorrect index name 'FkmyDomain1AttributeValue'

in both cases (create table/alter table syntax).

So, was that a problem of MySql v5.0 which has been fixed in newer versions?

Could anybody please comment on this?

relationship tables (1 reply)

mvpfootball consulting — Mon, 23 Jul 2012 17:56:54 +0000

Hi everyone,

Im in need of help to understand how can I structure my database with multiple tables.

I have a database and here are the tables I created

1-players
2-testings
3-position
4-stats

My main table will be the player table and I want all the other table to be link to that one.

lets say I create a player on table one, I want to link that table to testing, position and stats tables. Because without the player there are no testing, no position and no stats.

I dont know if my question is clear? If it is how do I create this?

Linking table for rows in the same table (1 reply)

D Harrison — Mon, 23 Jul 2012 18:05:01 +0000

Hello, I apologize if this is a very newbie question. I've tried to find answers here and on Google and I may just be ignorant of how to frame the search.

I need to create a linking table that identifies a bi-lateral relationship between entities in the same table. As an example, consider a table of users where I want to indicate a partnership relationship between user 100 and user 1.

I've already realized I need a composite key, or I risk having this happen:
Id User1 User2
1 1 100
2 1 100

So, two rows with two different primary keys relating the same data.

However, even if I use a primary key of user1+user2, how do I avoid this:
User1 User2
1 100
100 1

Any theoretical or practical advice would be much appreciated.

Thank you in advance.

Zero Instead of Null Vs Two Tables. (no replies)

Bryce Byrd — Fri, 27 Apr 2012 19:23:28 +0000

I'm working on refactoring a database(s) we have, it involves over 100 separate databases with 30 plus tables in each database. Each table is exactly the same schema (Code VARCHAR(10), Description (VARCHAR(75)). If I add a key (ID2) to indicate which of the thirty odd tables a one of these records belonged too, I get get it down to one table per database. I can then add a key (ID1) to indicate which database the tables came from and take it all down to one table (not counting the database name / table name reference tables).

So my schema will end up beign (ID1 Int, ID2 Int, Code VARCHAR(10), Description VARCHAR(10). I have a question because a number of the Code tables are identical in each database, and they will not ever vary.

My initial thought was to have two tables. One wouldn't have ID1 on it, and would be used to hold those code tables that didn't vary from database to database and I'd have the table with ID1 that would hold those that did. I am, however, not very keen on having to maintain to tables that look so similar. How bad would it be to add to the database names table a "0" key value then using 0 as the ID1 for all code tables that didn't vary from database to database.

Thanks

- Bryce

errors with foreign keys (1 reply)

Crispy X — Sat, 28 Apr 2012 08:20:22 +0000

Hello all,

I keep receiving these errors when attempting to create a few tables. I cannot for the life me figure out what I am missing.

ERROR 1005 (HY000): Can't create table './user/EMPLOYEE.frm' (errno: 150)
ERROR 1005 (HY000): Can't create table './user/VEHICLE.frm' (errno: 150)
ERROR 1005 (HY000): Can't create table './user/EQUIPMENT.frm' (errno: 150)

CREATE TABLE DIR_INTELLIGENCE
(DIR_I_DEPT DECIMAL(4,0) PRIMARY KEY,
DIR_I_BUDGET DECIMAL(7,2),
DIR_I_CREDITLIMIT DECIMAL(7,2));
CREATE TABLE NATIONAL_C_SVC
(NAT_C_DEPT DECIMAL(4,0) PRIMARY KEY,
NAT_C_BUDGET DECIMAL(7,2) NOT NULL,
NAT_C_CREDITLIMIT DECIMAL(7,2));
CREATE TABLE DIR_SCIENCE_TECH
(DIR_S_T_DEPT DECIMAL(4,0) PRIMARY KEY,
DIR_S_T_BUDGET DECIMAL(7,2),
DIR_S_T_CREDITLIMIT DECIMAL(7,2));
CREATE TABLE DIR_SUPPORT
(DIR_S_DEPT DECIMAL(4,0) PRIMARY KEY,
DIR_S_BUDGET DECIMAL(7,2),
DIR_S_CREDITLIMIT DECIMAL(7,2));
CREATE TABLE EMPLOYEE
(DIR_S_DEPT DECIMAL(4,0),
NAT_C_DEPT DECIMAL(4,0),
DIR_I_DEPT DECIMAL(4,0),
DIR_S_T_DEPT DECIMAL(4,0),
EMP_LNAME CHAR(15),
EMP_FNAME CHAR(15),
EMP_POSITION CHAR(15),
EMP_ADDR CHAR(15),
EMP_CITY CHAR(20),
EMP_ZIP DECIMAL(5,0),
EMP_PHONE DECIMAL(10,0),
EMP_EQUIP CHAR(15),
EMP_VEHICLE CHAR(3),
FOREIGN KEY (DIR_S_DEPT) REFERENCES DIR_SUPPORT (DIR_S_DEPT),
FOREIGN KEY (NAT_C_DEPT) REFERENCES NATIONAL_CLANDESTINE_SVC (NAT_C_DEPT),
FOREIGN KEY (DIR_I_DEPT) REFERENCES DIR_INTELLIGENCE (DIR_I_DEPT),
FOREIGN KEY (DIR_S_T_DEPT) REFERENCES DIR_SCIENCE_TECH (DIR_S_T_DEPT),
PRIMARY KEY (EMP_LNAME, EMP_FNAME, DIR_S_DEPT, NAT_C_DEPT, DIR_I_DEPT, DIR_S_T_DEPT) );
CREATE TABLE VEHICLE
(DIR_S_DEPT DECIMAL(4,0),
NAT_C_DEPT DECIMAL(4,0),
DIR_I_DEPT DECIMAL(4,0),
DIR_S_T_DEPT DECIMAL(4,0),
EMP_LNAME CHAR(15),
EMP_FNAME CHAR(15),
VEH_TYPE CHAR(4),
VEH_PLATE CHAR(8),
VEH_VIN DECIMAL(20,0),
VEH_MODEL CHAR(10),
VEH_MANU CHAR(10),
FOREIGN KEY (DIR_S_DEPT) REFERENCES DIR_SUPPORT (DIR_S_DEPT),
FOREIGN KEY (NAT_C_DEPT) REFERENCES NATIONAL_CLANDESTINE_SVC (NAT_C_DEPT),
FOREIGN KEY (DIR_I_DEPT) REFERENCES DIR_INTELLIGENCE (DIR_I_DEPT),
FOREIGN KEY (DIR_S_T_DEPT) REFERENCES DIR_SCIENCE_TECH (DIR_S_T_DEPT),
FOREIGN KEY (EMP_LNAME) REFERENCES EMPLOYEE (EMP_LNAME),
FOREIGN KEY (EMP_FNAME) REFERENCES EMPLOYEE (EMP_FNAME),
PRIMARY KEY (DIR_S_DEPT, NAT_C_DEPT, DIR_I_DEPT, DIR_S_T_DEPT, VEH_TYPE) );
CREATE TABLE EQUIPMENT
(DIR_S_DEPT DECIMAL(4,0),
NAT_C_DEPT DECIMAL(4,0),
DIR_I_DEPT DECIMAL(4,0),
DIR_S_T_DEPT DECIMAL(4,0),
EMP_LNAME CHAR(15),
EMP_FNAME CHAR(15),
EQUIP_TYPE CHAR(20),
EQUIP_ISSUED_ON DATE,
EQUIP_RETURN_ON DATE,
EQUIP_OVERDUE CHAR(3),
EMP_EQUIP CHAR(3),
FOREIGN KEY (DIR_S_DEPT) REFERENCES DIR_SUPPORT (DIR_S_DEPT),
FOREIGN KEY (NAT_C_DEPT) REFERENCES NATIONAL_CLANDESTINE_SVC (NAT_C_DEPT),
FOREIGN KEY (DIR_I_DEPT) REFERENCES DIR_INTELLIGENCE (DIR_I_DEPT),
FOREIGN KEY (DIR_S_T_DEPT) REFERENCES DIR_SCIENCE_TECH (DIR_S_T_DEPT),
FOREIGN KEY (EMP_LNAME) REFERENCES EMPLOYEE (EMP_LNAME),
FOREIGN KEY (EMP_FNAME) REFERENCES EMPLOYEE (EMP_FNAME),
PRIMARY KEY (DIR_S_DEPT, NAT_C_DEPT, DIR_I_DEPT, DIR_S_T_DEPT, EQUIP_TYPE) );

Sql execution error #1005 .response from the database (1 reply)

giri giri — Sun, 08 Apr 2012 21:19:31 +0000

Hello,

I am getting "SQL execution error #1005.Response from the database" error no 121 error when i execute the below sql scripts.

The foreign key named "fk_category_id" exists in both subcategory table and property table .The error is getting resolved when i name the foreign keys in these 2 tables differently .

So my question is should the foreign key names across tables be unique in mysql ??

is there any other reason that this error can occur??

CREATE TABLE IF NOT EXISTS `sampledb`.`category` (
`category_id` INT NOT NULL ,
PRIMARY KEY (`category_id`) )

CREATE TABLE IF NOT EXISTS `sampledb`.`subcategory` (
`subcategory_id` INT NOT NULL ,
`category_id` INT NULL ,
PRIMARY KEY (`subcategory_id`) ,
CONSTRAINT `fk_category_id`
FOREIGN KEY (`category_id` )
REFERENCES `realtydb`.`category` (`category_id` ))

CREATE TABLE IF NOT EXISTS `sampledb`.`property` (
`property_id` INT NOT NULL ,
`category_id` INT NULL ,
PRIMARY KEY (`property_id`) ,
CONSTRAINT `fk_category_id`
FOREIGN KEY (`category_id` )
REFERENCES `realtydb`.`category` (`category_id` ))

"Indirect" foreign key (no replies)

Ivar Kråbøl — Fri, 23 Mar 2012 22:41:18 +0000

This is for a game.

Simply put, there are several races, each consisting of several subraces. Some careers can only be pursued by character of the approriate race, but the character table contains only the subrace, as to avoid redundancy.

Quote
Not actual code, just to illustrate the setup

TABLE `race` (
`race_id` INT,
PRIMARY KEY (`race_id`)
)

TABLE `subrace` (
`subrace_id` INT,
`race_id` INT,
PRIMARY KEY (`subrace_id`),
FOREIGN KEY (`race_id`) REFERENCES `race`(`race_id`)
)

TABLE `career` (
`career_id` INT,
PRIMARY KEY (`career_id`)
)

TABLE `career_eligible_race` (
`career_id` INT,
`race_id` INT,
PRIMARY KEY (`career_id`, `race_id`),
FOREIGN KEY (`career_id`) REFERENCES `career`(`career_id`),
FOREIGN KEY (`race_id`) REFERENCES `race`(`race_id`)
)

TABLE `character` (
`character_id` INT,
`subrace_id` INT,
`career_id` INT,
PRIMARY KEY (`character_id`),
FOREIGN KEY (`subrace_id`) REFERENCES `subrace`(`subrace_id`),
FOREIGN KEY (`career_id`) REFERENCES `career`(`career_id`)
)

Now, I want to make a constraint that makes sure that a character with a given subrace is eligible to pursue his career.

The only option I could think of was adding a race attribute to the character table, and constrain it with a composite foreign key of both race and subrace, referencing the subrace table. But again, redundancy.

como puedo hacer que un tabla se relacione dos veces con otra tabla. (no replies)

Oscar Ordoñez Mego — Tue, 14 Feb 2012 02:32:56 +0000

bueno mi dua es que tengo tabla1 con los campos idtabla1 , nombre y tengo la tabla2 con los campos idtabla2, idtabla1_1,idtabla1_2,nombre. lo que quiero es relacionar los campos idtabla1_1,idtabla1_2 de la tabla2 con el campo idtabla1 de la tabla1.

mi codigo es el siguiedrop database if exists prueba;

create database prueba;

use prueba;

create table tabla1
(
idtabla1 integer primary key,
nombre varchar(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tabla1 values(1,'oscar');
insert into tabla1 values(2,'Carlos');

create table tabla2
(
idtabla2 integer ,
idtabla1_1 integer ,
idtabla1_2 integer,
nombre varchar (50),
primary key(idtabla2,idtabla1_1,idtabla1_2),
foreign key(idtabla1_1,idtabla1_2)
references tabla1(idtabla1)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tabla2 values (1,1,2,'oskitar');

y me bota error 1239

por favor ayudame resien estoy empesando con mysql

la imegen del modelo de la tabla

http://img716.imageshack.us/img716/246/relan.png

dropping a foreign key (no replies)

adams johnson — Mon, 23 Jan 2012 10:20:58 +0000

pls i need urgent help on this one.
i creeted a table using InnoDb as my engine since MyIsam is not giving me what I want.
here is the create table statements;
create table staff
(
staff_id int,
staff_name varchar(10),
primary key (staff_id)
) engine = InnoDB;

create table student
(
reg_no int,
name varchar(10),
staff_adviser int,
primary key(reg_no),
constraint fk_student foreign key(staff_adviser) references staff(staff_id) on update cascade on delete cascade) engine = InnoDB;

the table creation was successful and all insertions and referential actions were also successful. but when I tried to drop the foreign key I used the statement

alter table student drop constraint fk_student;
and i got this error:
error 1064(42000) you hve an error in your sql syntax; check the manual that correspond to your mysql server versio for the right syntax to use near 'constraint fk_student' at line 1.

then I tried to use this sytax;
alter table student drop fk_student;
and I got this error;
erroe 1091 (4200) :can't drop 'fk_student' check that the column/key exists.

then I used this;
alter table student drop foreign key;
and i got the error:
error 1005 (HY000): can't create table 'school.#sql-13fc_1 (errno150)

but when I used this sytax;
alter table student drop foreign key fk_student;
it say query ok.
and when desscribed the table i.e describe student:
i still saw the foreign key there.

Deleting Records (1 reply)

Sushmita Mishra — Tue, 24 Jan 2012 09:04:11 +0000

Hi ,

I have tables which hold foreign key constraint to maintain referentialintegerity among two tables. I have defined On Delete Cascade and On Update Cascade in my table defination on the key.
Now when I am deleting a record from the parent table, the record is getting deleted without any change made to the child table, thus violating the integrity of the system.
This is what I am facing on mysql5.1.x
Is there any server configuration required to remove this issue?
What is the version of mysql which has overcome this issue??

Thanks in advance.

dropping foreign keys (5 replies)

adams johnson — Wed, 11 Jan 2012 09:55:12 +0000

i created a table and added a foreign key. but the problem is that i cant drop the foreign key. whenever I try it says query o.k but when i check the table, the foreign key is still there. here are the two tables;
create table students(reg_no int, name varchar(10), staff_adv int, primary key (reg_no)) engine = InnoDB;
create table staff (staff_id int, name varchar(10), student_with int, primary key (student_with)) engine = InnoDB;
then i added the foreign key thus;
alter table staff add constraint fk_staff foreign key(student_with) references students on update cascade on delete set null;
but when i try to drop the foreign key like this;
alter table staff drop foreign key fk_staff;
it says query ok, 2 rows affected <0.09sec>
but if I describe the table i still see the foreign key. pls what should I do?

What is the best way to remove all Foreign Keys for a refactoring (no replies)

Noah Goodrich — Tue, 18 Oct 2011 16:18:40 +0000

We are currently working on a major refactoring of the database and as part of the process, I need to remove all of the existing Foreign Key Contraints and create new ones.

Right now I have the following code to find and remove the constraints:

$query = "
SELECT DISTINCT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.key_column_usage
WHERE CONSTRAINT_SCHEMA = 'optimus'
AND REFERENCED_TABLE_NAME IS NOT NULL
";

$result = $this->_query($query);

foreach ($result as $row) {
echo "\tDropping {$row->TABLE_NAME}.{$row->CONSTRAINT_NAME}\n";
$this->_query("
ALTER TABLE `{$row->TABLE_NAME}`
DROP FOREIGN KEY `{$row->CONSTRAINT_NAME}`;
");
}

But this creates two problems I have not been able to solve:

a) All of the tables still have the original KEYS that were created for the foreign key contraints. I tried adding a DROP KEY {$row->CONSTRAINT_NAME} to the ALTER TABLE statement but then I received errors that the KEY did not exist.

b) On altering one particular table to add in the new constraint I receive this error which is completely new to me:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
111018 9:37:00 TRANSACTION:
TRANSACTION 0 797208, ACTIVE 3 sec, PROCESS NO 1079, OS thread id 140091990087424 inserting, thread declared inside INNODB 228
mysql TABLES IN USE 2, locked 2
317 LOCK struct(s), HEAP size 47088, 59493 ROW LOCK(s), UNDO LOG entries 53994
MySQL thread id 803, QUERY id 57650 localhost 127.0.0.1 root copy TO tmp TABLE
ALTER TABLE answers_users
RENAME TO answers,
CHANGE COLUMN answer_id option_id INT(11) UNSIGNED NOT NULL,
ADD CONSTRAINT `fk-user-answers` FOREIGN KEY (user_id) REFERENCES clients(id),
ADD CONSTRAINT `fk-question-answers` FOREIGN KEY (question_id) REFERENCES questions(id),
ADD CONSTRAINT `fk-option-answers` FOREIGN KEY (option_id) REFERENCES OPTIONS(id)
FOREIGN KEY CONSTRAINT fails FOR TABLE `optimus`.:
,
CONSTRAINT `fk-option-answers` FOREIGN KEY (`option_id`) REFERENCES `options` (`id`)
Trying TO ADD IN child TABLE, IN INDEX `fk-user_answer-answers` tuple:
DATA TUPLE: 2 FIELDS;
0: len 4; HEX 00000000; ASC ;; 1: len 4; HEX 0000fb72; ASC r;;

But IN parent TABLE `optimus`.`options`, IN INDEX `PRIMARY`,
the closest MATCH we can find IS record:
PHYSICAL RECORD: n_fields 8; COMPACT FORMAT; info bits 0
0: len 4; HEX 00000062; ASC b;; 1: len 6; HEX 0000000c29bb; ASC ) ;; 2: len 7; HEX 0000000a3e093b; ASC > ;;; 3: len 4; HEX 00000016; ASC ;; 4: len 25; HEX 28373230206f72204869676865722920457863656c6c656e74; ASC (720 OR Higher) Excellent;; 5: len 4; HEX 00000001; ASC ;; 6: len 4; HEX 800002d0; ASC ;; 7: len 1; HEX 80; ASC ;;

Is there a better way to clean out the old foreign keys? If not my next thought is to first convert the tables to MyISAM then drop the KEYS and then convert them back to Innodb.

Thoughts?

how to get only parent table record if no record in child row using Join (1 reply)

ROHIT GUPTA — Mon, 17 Oct 2011 05:45:06 +0000

Hello,

I am stuck with one scnerio where i have to prepare reports of all inquiries.

i have several inquiries in Parent Table.

For each inquiry there are materials in Inquiry_material table

for each material in inquiry_material there are several sites in table inquiry_site

and for each site in inquiry_site there are several services in inquiry_services table.

Now i have some inquiries with no material and some inquiries with material but no site and sone site with no services.

i wants to get all data from inquiry using join with child records and if there is no child record then blank record should be returned, i am trying my best to get the same but i think i am making some silly mistake and getting only those records which are having complete child records.

Please suggest me to get empty records if there are is no record in child table.

i am using this query.

SELECT
inq.Inquiry_companym_code,
inq.inquiry_number,
imd.InquiryMaterialDetails_material_quantity,
isd.InquirySiteDetails_site_code,
issd.InquiryServiceDetails_service_code
FROM inquiry inq
JOIN inquiry_material imd
ON inq.Inquiry_ID = imd.InquiryMaterialDetails_inquiry_id
INNER JOIN inquiry_site isd
ON imd.InquiryMaterialDetails_id = isd.InquirySiteDetails_inquirymaterialdetails_id
INNER JOIN inquiry_service issd
ON issd.InquiryServiceDetails_inquirysitedetails_id = isd.InquirySiteDetails_id;

Problem with Foreign key creator (1 reply)

itzik orfan — Mon, 17 Oct 2011 06:01:53 +0000

Hi
I am sitting on a program that need a some tables and I link between the tables.
some of the FK work just fine but the one alwayes send me an error like i try to create a table ???
the tables are:

//table that hold a list of servers every server has a lot of application
CREATE TABLE IF NOT EXISTS
`TestingApp`.`Server` (
`ServerName` VARCHAR(20) NOT NULL,
`ServerPort` VARCHAR(5) NOT NULL,
`ServerType` int NOT NULL,
`User` VARCHAR(20) NOT NULL,
`Password` VARCHAR(20) NOT NULL,
PRIMARY KEY (`ServerName`, `ServerPort`))
//work Fine

//application that connect to server

CREATE TABLE IF NOT EXISTS
`TestingApp`.`App` (
`AppName` VARCHAR(20) NOT NULL,
`DisplayName` VARCHAR(20) NOT NULL,
`ServerPort` VARCHAR(5) NOT NULL,
`ServerName` VARCHAR(20) NOT NULL,
PRIMARY KEY (`BiAppName`))
///work fine

//the fk
ALTER TABLE App
ADD CONSTRAINT FK_App
FOREIGN KEY (ServerName) REFERENCES Srever (ServerName)

the error i get is:
error Can't create table 'testingapp.#sql-1758_18' (errno: 150)
i dont want to create table!!! i want to creat FK
Help Please
Thank you
IziaMan

Foreign key constraint failed when trying to add data to the normalised DB (no replies)

Chelsea Sanders — Fri, 09 Sep 2011 08:37:58 +0000

Hi there!

I'm working on a web portal that links to a database - users are able to upload and search for data within the database. I am working with XAMPP and the Server version is 5.5.8

I am trying to create a normalised database with foreign keys by using InnoDB. I am currently adding data to the DB and the relations I have created I don't think are correct.

I am not sure if I'm just being silly and the answer is really simple - but I just keep on going in circles. :wtf:

It would be great if some one could help me out!

There are two relationships in question:

QUESTION ONE.

tables involved are orders, industry_sector, and industry_sub_sector. The industry_sector table has an optional 1:m relationship with the industry_sub_sector. orders table has a mandatory m:1 relationship with the industry_sector table. The industry_sector and the industry_sub_sector tables are complete as those are the only possible values to ever be included. The orders table so far is empty as I don't think I will be able to reference which IndustrySubSectorValue that particular OrderID will have.

Possible Solution 1: I know that I could add another field to the orders table - IndustrySubSectorID - however that would create many NULL values as each order doesnt have to have a IndustrySubSectorValue (the relationship is mandatory).

Possible Solution 2: I could make a bridge entity that would include the following fields: OrdersID, IndustySectorID, IndustrySubSectorID. However that would be creating a relationship with orders. Itsn't that incorrect as industry_sub_sector table should have a relationship industry_sector NOT orders?!

I am not sure if I'm just being silly and the answer is really simple - but I just keep on going in circles. It would be great if you could help me out.

QUESTION 2.

This question is similar to question 1 in that it is about relationship between 3 interlinked tables, namely: orders, distribution_channel, and distribution_channel_details.

The distribution_channel table has a 1:m mandatory relationship with orders table. The distribution_channel_details has an optional 1:m relationship with the distribution_channel table. (basically distribution channel details are the 'notes' on how that particular distribution channel was used for that specific order)

Possible solution would be to do the same as question 1 - but that would create null values as each order doesnt require a DistributionDetailValue.

So basically: I am unable to add rows to the orders table because foreign key constraint failed. The relationships I have in place are wrong. I am looking for help with the relationships between:

1. orders, industry_sector, and industry_sub_sector
2. orders, distribution_channel, and distribution_channel_details

I realise I could easily add both DistributionChannelDetailID and IndustrySubSectorID to the orders table - however that would creat a large amount of NULL values as both DistributionChannelDetailValue and IndustrySubSectorValue are optional for each order added.

So simply put : do I create a bridge entity that is directly linked to the orders table for the industry_sub_sector and the distribution_channel_details respectively ??

If you need any more detail about either question please let me know, and sorry if I havent explained it all to clearly ;)

Thanks so much! Chelsea

cyclic reference (no replies)

Boris Murashin — Wed, 31 Aug 2011 19:53:56 +0000

Hello!

I'm in doubt about my model. Here it is:
http://i29.fastpic.ru/big/2011/0831/ee/3a04def71dd8ac99cd90a97944f976ee.png

`Products` can pertain to multiple `Categories`, hence n:m relationship `Categories_has_Products`. Each category has a set of `CategoryParams`, mandatory for each product, that belongs to that category (there's no a priori info about how many and which params a category can get and list may change frequently).
Then, when I'm adding a table `ProductsParams` to list params values for products, i get references to both `Products` and `CategoriesParams`. And a kind of cycle shows up in FKs.

I have no idea how not to make such a cycle...

ALTER TABLE NOCHECK CONSTRAINT (no replies)

Mark Oberg — Thu, 25 Aug 2011 13:50:28 +0000

Does MYSQL support the use of this SQL statement:
ALTER TABLE `tablename` NOCHECK CONSTRAINT `foreignkeyname`;

In essence I wish to have a foreign key constraint between two tables, but want to not have it actually be enforced. Think of it as a drop-down type box filled with values from a related table, but the user is able to type in it freely and not be limited to the list.

Thanks in advance,
Mark

on delete cascade differences (no replies)

muhamad eawedat — Tue, 23 Aug 2011 22:41:45 +0000

hey

what are the differences, if there are among these three statements?

Code:

key xyz(sid), constraint xyz foreign key(sid) references Singers(id) on delete cascade;

Code:

foreign key(sid) references Singers(id) on delete cascade;

Code:

index idx_dr(sid),foreign key(sid) references Singers(id) on delete cascade;

thanks

issues with table joins (no replies)

Gaurav J — Mon, 08 Aug 2011 02:18:41 +0000