Ошибка связь уже существует phpmyadmin

Здравствуйте. Создала таблицы: Страны, Страны-туры, Туры. Связала таблицы Страны и Страны-туры по Id,а вот связать Страны-туры и Туры уже не получилось. Связывала по одному и тому же принципу,но при создании второй связи выдает следующее:

1
2
3
4
5
6
7
8
9
10
11

SQL-запрос:
 
ALTER TABLE  `Тур-страна` ADD FOREIGN KEY (  `tour_id` ) REFERENCES  `admin`.`Тур` (
 
`Id`
) ON DELETE CASCADE ON UPDATE CASCADE ;
 
 
Ответ MySQL: Документация
 
#1050 - Table '.admin@y0@z0@w0@002d@s0@y1@x0@y0@u0' already exists

Подскажите,пожалуйста, неужели в одной таблице может быть только один атрибут связан? Связывала атрибуты так: в таблице Страны Id имеет тип Int и автоинкремент. В таблице Страны-туры атрибут Id_country,который ссылается на атрибут Id таблицы Страны , имеет по умолчанию NULL и является индексом, далее шла в «Связи», соединяла соответствующие атрибуты и было все ок. Проделала те же действия с таблицами «Туры-страны» и Туры, но выдало вышеизложенное сообщение. Буду благодарна за ответы.

Вы не вошли. Пожалуйста, войдите или зарегистрируйтесь.

Активные темы Темы без ответов

-- phpMyAdmin SQL Dump
-- version 2.11.4
-- http://www.phpmyadmin.net
--
-- Хост: localhost
-- Время создания: Май 17 2011 г., 22:25
-- Версия сервера: 5.0.51
-- Версия PHP: 5.2.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- База данных: `computer_shop`
--

-- --------------------------------------------------------

--
-- Структура таблицы `computer`
--

CREATE TABLE IF NOT EXISTS `computer` (
  `ID_Computer` int(10) unsigned NOT NULL auto_increment,
  `Processor` varchar(20) character set utf8 collate utf8_unicode_ci NOT NULL,
  `Chastota` double NOT NULL,
  `RAM` int(11) NOT NULL,
  `Model` varchar(20) character set utf8 collate utf8_unicode_ci NOT NULL,
  `Release_Date` date NOT NULL,
  PRIMARY KEY  (`ID_Computer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Дамп данных таблицы `computer`
--

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- База данных: `computer_shop`
--

-- --------------------------------------------------------

--
-- Структура таблицы `firms`
--

CREATE TABLE IF NOT EXISTS `firms` (
  `ID_Firm` int(11) NOT NULL auto_increment,
  `Firm_name` varchar(30) default NULL,
  `Adres` varchar(50) default NULL,
  PRIMARY KEY  (`ID_Firm`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Дамп данных таблицы `firms`
--

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- База данных: `computer_shop`
--

-- --------------------------------------------------------

--
-- Структура таблицы `market_supply`
--

CREATE TABLE IF NOT EXISTS `market_supply` (
  `ID_Supply` int(10) unsigned NOT NULL auto_increment,
  `Number_comp` int(10) unsigned NOT NULL,
  `Price` int(10) unsigned NOT NULL,
  `FK_Computer` int(10) unsigned NOT NULL,
  `FK_Firms` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`ID_Supply`),
  KEY `FK_Computer` (`FK_Computer`),
  KEY `FK_Firms` (`FK_Firms`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Дамп данных таблицы `market_supply`
--


--
-- Ограничения внешнего ключа сохраненных таблиц
--

--
-- Ограничения внешнего ключа таблицы `market_supply`
--
ALTER TABLE `market_supply`
  ADD CONSTRAINT `market_supply_ibfk_1` FOREIGN KEY (`FK_Computer`) REFERENCES `computer` (`ID_Computer`) ON UPDATE NO ACTION;

Сообщения 12

The table2 foreign key constraint means that any table2 customerId value must appear as a customerId in table1. You are getting the error because you are inserting a customerID into table2 that doesn’t appear in table1.

Since the DBMS is generating table1 customerIDs by auto increment, if you insert a row you have to get that value in order to insert a row using that customerID into table2.

I guess you say «I already established a relationship between table1 and table2» to mean «I declared a foreign key constraint». And I guess you think that means «after I insert into table1 the DBMS will use the auto-generated key value as the foreign key value when I insert into table2». But it doesn’t mean that. You have to do that yourself. The foreign key constraint just means that the DBMS checks that every table2 customerId value appears as a table1 customerId value.

You can and must use any previously inserted key value as the corresponding value when you insert into a table with a foreign key to that key.

To get back the auto incremented key value generated by the DBMS use LAST_INSERT_ID():

INSERT INTO table1 (CustomerName,Address,State)
VALUES('value1','value2','value3');
INSERT INTO table2 (customerId,product,cost)
VALUES(LAST_INSERT_ID(),'valueA','valueB');

This is what it is for. But here are the problems if you don’t use it.

First, if you are not in a serialized transaction then you must use LAST_INSERT_ID(). Because after your table1 insert but before your table2 insert others could have added rows and/or deleted rows including your new row and/or changed rows including your new row. So you cannot rely on querying table1 after its insert get some customerId value that you know you added.

Second, suppose you are in a serialized transaction and you don’t use LAST_INSERT_ID().

If (CustomerName,Address,State) is also a superkey of table1, ie its values are unique, ie SQL UNIQUE/KEY/PK is declared on all or some of its columns, then you can use it to query for the associated new customerId:

set @customerId = (
    SELECT customerId
    FROM table1
    WHERE CustomerName = 'value1'
    AND Address = 'value2'
    AND State = 'value3');
INSERT INTO table2 (customerId,product,cost)
VALUES(@customerId,'valueA','valueB');

But if (CustomerName,Address,State) is not a superkey of table1 then you cannot do this. Because other rows that are duplicates for that subrow could be in table1. So you could get multiple rows back. So you would not know which is the newest one. Instead you have to query table1 before the insert, then insert, then find the difference between the old and new sets of customerIds:

CREATE TEMPORARY TABLE table1old (
    customerId (int) PRIMARY KEY
    );
INSERT INTO table1old
SELECT customerId FROM table1;

INSERT INTO table1 (CustomerName,Address,State)
VALUES('value1','value2','value3');

set @customerId = (
    SELECT customerId
    FROM table1
    WHERE CustomerName NOT IN table1old);
INSERT INTO table2 (customerId,product,cost)
VALUES(@customerId,'valueA','valueB');

Just use LAST_INSERT_ID().

PS: Interestingly, given the table definitions, ideally one could write:

INSERT INTO (
    SELECT CustomerName,Address,State,A,B
    FROM table1 JOIN table2
    USING (CustomerId))
VALUES('value1','value2','value3','valueA','valueB')

since there is just one pair of new table1 & table2 values that can result. There are some legal updates through views in SQL, although none involving multiple tables in MySQL currently

The table2 foreign key constraint means that any table2 customerId value must appear as a customerId in table1. You are getting the error because you are inserting a customerID into table2 that doesn’t appear in table1.

Since the DBMS is generating table1 customerIDs by auto increment, if you insert a row you have to get that value in order to insert a row using that customerID into table2.

I guess you say «I already established a relationship between table1 and table2» to mean «I declared a foreign key constraint». And I guess you think that means «after I insert into table1 the DBMS will use the auto-generated key value as the foreign key value when I insert into table2». But it doesn’t mean that. You have to do that yourself. The foreign key constraint just means that the DBMS checks that every table2 customerId value appears as a table1 customerId value.

You can and must use any previously inserted key value as the corresponding value when you insert into a table with a foreign key to that key.

To get back the auto incremented key value generated by the DBMS use LAST_INSERT_ID():

INSERT INTO table1 (CustomerName,Address,State)
VALUES('value1','value2','value3');
INSERT INTO table2 (customerId,product,cost)
VALUES(LAST_INSERT_ID(),'valueA','valueB');

This is what it is for. But here are the problems if you don’t use it.

First, if you are not in a serialized transaction then you must use LAST_INSERT_ID(). Because after your table1 insert but before your table2 insert others could have added rows and/or deleted rows including your new row and/or changed rows including your new row. So you cannot rely on querying table1 after its insert get some customerId value that you know you added.

Second, suppose you are in a serialized transaction and you don’t use LAST_INSERT_ID().

If (CustomerName,Address,State) is also a superkey of table1, ie its values are unique, ie SQL UNIQUE/KEY/PK is declared on all or some of its columns, then you can use it to query for the associated new customerId:

set @customerId = (
    SELECT customerId
    FROM table1
    WHERE CustomerName = 'value1'
    AND Address = 'value2'
    AND State = 'value3');
INSERT INTO table2 (customerId,product,cost)
VALUES(@customerId,'valueA','valueB');

But if (CustomerName,Address,State) is not a superkey of table1 then you cannot do this. Because other rows that are duplicates for that subrow could be in table1. So you could get multiple rows back. So you would not know which is the newest one. Instead you have to query table1 before the insert, then insert, then find the difference between the old and new sets of customerIds:

CREATE TEMPORARY TABLE table1old (
    customerId (int) PRIMARY KEY
    );
INSERT INTO table1old
SELECT customerId FROM table1;

INSERT INTO table1 (CustomerName,Address,State)
VALUES('value1','value2','value3');

set @customerId = (
    SELECT customerId
    FROM table1
    WHERE CustomerName NOT IN table1old);
INSERT INTO table2 (customerId,product,cost)
VALUES(@customerId,'valueA','valueB');

Just use LAST_INSERT_ID().

PS: Interestingly, given the table definitions, ideally one could write:

INSERT INTO (
    SELECT CustomerName,Address,State,A,B
    FROM table1 JOIN table2
    USING (CustomerId))
VALUES('value1','value2','value3','valueA','valueB')

since there is just one pair of new table1 & table2 values that can result. There are some legal updates through views in SQL, although none involving multiple tables in MySQL currently