Mixing
Mysql duplicate foreign key constraint
When I try to import a database I get this error
SQL query:
ALTER TABLE `bid`
ADD CONSTRAINT `bid_ibfk_4` FOREIGN KEY (`auction_contact_id`) REFERENCES `auction_contact` (`auction_contact_id`),
ADD CONSTRAINT `bid_ibfk_3` FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`)
MySQL said: Documentation
#1826 - Duplicate foreign key constraint name 'projekt_classics/bid_ibfk_3'
Looking at all the foreign keys I get this as a result
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY'
result
def projekt_classics bid_ibfk_2 projekt_classics bid FOREIGN KEY
def projekt_classics bid_ibfk_3 projekt_classics bid FOREIGN KEY
def projekt_classics car_ibfk_1 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_3 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_4 projekt_classics car FOREIGN KEY
def projekt_classics car_brand_ibfk_1 projekt_classics car_brand FOREIGN KEY
searching into the sql the bid_ibfk_3 constraint shows only 1 time.
All the data is in the imported database but I wonder how I can avoid this error.
EDIT:
First dropping all the tables runs the query without problems.
I export my database using PHPmyadmin.
I guess the error was because of the foreign key constraint not yet deleted before trying to create it again.
mysql
asked Sep 15 '16 at 1:23
anatakanatak
1551314
add a comment |
When I try to import a database I get this error
SQL query:
ALTER TABLE `bid`
ADD CONSTRAINT `bid_ibfk_4` FOREIGN KEY (`auction_contact_id`) REFERENCES `auction_contact` (`auction_contact_id`),
ADD CONSTRAINT `bid_ibfk_3` FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`)
MySQL said: Documentation
#1826 - Duplicate foreign key constraint name 'projekt_classics/bid_ibfk_3'
Looking at all the foreign keys I get this as a result
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY'
result
def projekt_classics bid_ibfk_2 projekt_classics bid FOREIGN KEY
def projekt_classics bid_ibfk_3 projekt_classics bid FOREIGN KEY
def projekt_classics car_ibfk_1 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_3 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_4 projekt_classics car FOREIGN KEY
def projekt_classics car_brand_ibfk_1 projekt_classics car_brand FOREIGN KEY
searching into the sql the bid_ibfk_3 constraint shows only 1 time.
All the data is in the imported database but I wonder how I can avoid this error.
EDIT:
First dropping all the tables runs the query without problems.
I export my database using PHPmyadmin.
I guess the error was because of the foreign key constraint not yet deleted before trying to create it again.
mysql
asked Sep 15 '16 at 1:23
anatakanatak
1551314
add a comment |
When I try to import a database I get this error
SQL query:
ALTER TABLE `bid`
ADD CONSTRAINT `bid_ibfk_4` FOREIGN KEY (`auction_contact_id`) REFERENCES `auction_contact` (`auction_contact_id`),
ADD CONSTRAINT `bid_ibfk_3` FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`)
MySQL said: Documentation
#1826 - Duplicate foreign key constraint name 'projekt_classics/bid_ibfk_3'
Looking at all the foreign keys I get this as a result
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY'
result
def projekt_classics bid_ibfk_2 projekt_classics bid FOREIGN KEY
def projekt_classics bid_ibfk_3 projekt_classics bid FOREIGN KEY
def projekt_classics car_ibfk_1 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_3 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_4 projekt_classics car FOREIGN KEY
def projekt_classics car_brand_ibfk_1 projekt_classics car_brand FOREIGN KEY
searching into the sql the bid_ibfk_3 constraint shows only 1 time.
All the data is in the imported database but I wonder how I can avoid this error.
EDIT:
First dropping all the tables runs the query without problems.
I export my database using PHPmyadmin.
I guess the error was because of the foreign key constraint not yet deleted before trying to create it again.
mysql
asked Sep 15 '16 at 1:23
anatakanatak
1551314
When I try to import a database I get this error
SQL query:
ALTER TABLE `bid`
ADD CONSTRAINT `bid_ibfk_4` FOREIGN KEY (`auction_contact_id`) REFERENCES `auction_contact` (`auction_contact_id`),
ADD CONSTRAINT `bid_ibfk_3` FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`)
MySQL said: Documentation
#1826 - Duplicate foreign key constraint name 'projekt_classics/bid_ibfk_3'
Looking at all the foreign keys I get this as a result
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY'
result
def projekt_classics bid_ibfk_2 projekt_classics bid FOREIGN KEY
def projekt_classics bid_ibfk_3 projekt_classics bid FOREIGN KEY
def projekt_classics car_ibfk_1 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_3 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_4 projekt_classics car FOREIGN KEY
def projekt_classics car_brand_ibfk_1 projekt_classics car_brand FOREIGN KEY
searching into the sql the bid_ibfk_3 constraint shows only 1 time.
All the data is in the imported database but I wonder how I can avoid this error.
EDIT:
First dropping all the tables runs the query without problems.
I export my database using PHPmyadmin.
I guess the error was because of the foreign key constraint not yet deleted before trying to create it again.
mysql
mysql
asked Sep 15 '16 at 1:23
anatakanatak
1551314
asked Sep 15 '16 at 1:23
anatakanatak
1551314
edited Sep 15 '16 at 7:26
anatak
asked Sep 15 '16 at 1:23
anatakanatak
1551314
asked Sep 15 '16 at 1:23
anatakanatak
1551314
asked Sep 15 '16 at 1:23
anatakanatak
1551314
1551314
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
If you look at the result of your query, the foreign key bid_ibfk_3 already exists. In fact it is in the second row of the result.
def projekt_classics bid_ibfk_2 projekt_classics bid FOREIGN KEY
--the row below is the foreign key that you are trying to create
def projekt_classics bid_ibfk_3 projekt_classics bid FOREIGN KEY
def projekt_classics car_ibfk_1 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_3 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_4 projekt_classics car FOREIGN KEY
def projekt_classics car_brand_ibfk_1 projekt_classics car_brand FOREIGN KEY
That's why you are getting the duplicate foreign key constraint name when you are trying to execute this:
ADD CONSTRAINT `bid_ibfk_3` FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`)
You can modify your query to check first if the foreign key that you are trying to create does not exist, before actually creating it.
IF NOT EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
AND CONSTRAINT_TYPE = 'FOREIGN KEY'
AND CONSTRAINT_NAME = 'bid_ibfk_3') THEN
ALTER TABLE `bid` ADD CONSTRAINT `bid_ibfk_3`
FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`);
END IF
answered Sep 15 '16 at 3:13
KaeLKaeL
3,00921847
2
Or, just remove the optional keywordCONSTRAINTand the symbol/identifer following it, and justADD FOREIGN KEY ...since InnoDB will automatically generate unique symbols for each. (+1)
– Michael - sqlbot
Sep 15 '16 at 4:06
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f39501899%2fmysql-duplicate-foreign-key-constraint%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you look at the result of your query, the foreign key bid_ibfk_3 already exists. In fact it is in the second row of the result.
def projekt_classics bid_ibfk_2 projekt_classics bid FOREIGN KEY
--the row below is the foreign key that you are trying to create
def projekt_classics bid_ibfk_3 projekt_classics bid FOREIGN KEY
def projekt_classics car_ibfk_1 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_3 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_4 projekt_classics car FOREIGN KEY
def projekt_classics car_brand_ibfk_1 projekt_classics car_brand FOREIGN KEY
That's why you are getting the duplicate foreign key constraint name when you are trying to execute this:
ADD CONSTRAINT `bid_ibfk_3` FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`)
You can modify your query to check first if the foreign key that you are trying to create does not exist, before actually creating it.
IF NOT EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
AND CONSTRAINT_TYPE = 'FOREIGN KEY'
AND CONSTRAINT_NAME = 'bid_ibfk_3') THEN
ALTER TABLE `bid` ADD CONSTRAINT `bid_ibfk_3`
FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`);
END IF
answered Sep 15 '16 at 3:13
KaeLKaeL
3,00921847
2
Or, just remove the optional keywordCONSTRAINTand the symbol/identifer following it, and justADD FOREIGN KEY ...since InnoDB will automatically generate unique symbols for each. (+1)
– Michael - sqlbot
Sep 15 '16 at 4:06
add a comment |
If you look at the result of your query, the foreign key bid_ibfk_3 already exists. In fact it is in the second row of the result.
def projekt_classics bid_ibfk_2 projekt_classics bid FOREIGN KEY
--the row below is the foreign key that you are trying to create
def projekt_classics bid_ibfk_3 projekt_classics bid FOREIGN KEY
def projekt_classics car_ibfk_1 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_3 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_4 projekt_classics car FOREIGN KEY
def projekt_classics car_brand_ibfk_1 projekt_classics car_brand FOREIGN KEY
That's why you are getting the duplicate foreign key constraint name when you are trying to execute this:
ADD CONSTRAINT `bid_ibfk_3` FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`)
You can modify your query to check first if the foreign key that you are trying to create does not exist, before actually creating it.
IF NOT EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
AND CONSTRAINT_TYPE = 'FOREIGN KEY'
AND CONSTRAINT_NAME = 'bid_ibfk_3') THEN
ALTER TABLE `bid` ADD CONSTRAINT `bid_ibfk_3`
FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`);
END IF
answered Sep 15 '16 at 3:13
KaeLKaeL
3,00921847
2
Or, just remove the optional keywordCONSTRAINTand the symbol/identifer following it, and justADD FOREIGN KEY ...since InnoDB will automatically generate unique symbols for each. (+1)
– Michael - sqlbot
Sep 15 '16 at 4:06
add a comment |
If you look at the result of your query, the foreign key bid_ibfk_3 already exists. In fact it is in the second row of the result.
def projekt_classics bid_ibfk_2 projekt_classics bid FOREIGN KEY
--the row below is the foreign key that you are trying to create
def projekt_classics bid_ibfk_3 projekt_classics bid FOREIGN KEY
def projekt_classics car_ibfk_1 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_3 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_4 projekt_classics car FOREIGN KEY
def projekt_classics car_brand_ibfk_1 projekt_classics car_brand FOREIGN KEY
That's why you are getting the duplicate foreign key constraint name when you are trying to execute this:
ADD CONSTRAINT `bid_ibfk_3` FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`)
You can modify your query to check first if the foreign key that you are trying to create does not exist, before actually creating it.
IF NOT EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
AND CONSTRAINT_TYPE = 'FOREIGN KEY'
AND CONSTRAINT_NAME = 'bid_ibfk_3') THEN
ALTER TABLE `bid` ADD CONSTRAINT `bid_ibfk_3`
FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`);
END IF
answered Sep 15 '16 at 3:13
KaeLKaeL
3,00921847
If you look at the result of your query, the foreign key bid_ibfk_3 already exists. In fact it is in the second row of the result.
def projekt_classics bid_ibfk_2 projekt_classics bid FOREIGN KEY
--the row below is the foreign key that you are trying to create
def projekt_classics bid_ibfk_3 projekt_classics bid FOREIGN KEY
def projekt_classics car_ibfk_1 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_3 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_4 projekt_classics car FOREIGN KEY
def projekt_classics car_brand_ibfk_1 projekt_classics car_brand FOREIGN KEY
That's why you are getting the duplicate foreign key constraint name when you are trying to execute this:
ADD CONSTRAINT `bid_ibfk_3` FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`)
You can modify your query to check first if the foreign key that you are trying to create does not exist, before actually creating it.
IF NOT EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
AND CONSTRAINT_TYPE = 'FOREIGN KEY'
AND CONSTRAINT_NAME = 'bid_ibfk_3') THEN
ALTER TABLE `bid` ADD CONSTRAINT `bid_ibfk_3`
FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`);
END IF
answered Sep 15 '16 at 3:13
KaeLKaeL
3,00921847
answered Sep 15 '16 at 3:13
KaeLKaeL
3,00921847
answered Sep 15 '16 at 3:13
KaeLKaeL
3,00921847
answered Sep 15 '16 at 3:13
KaeLKaeL
3,00921847
3,00921847
2
Or, just remove the optional keywordCONSTRAINTand the symbol/identifer following it, and justADD FOREIGN KEY ...since InnoDB will automatically generate unique symbols for each. (+1)
– Michael - sqlbot
Sep 15 '16 at 4:06
add a comment |
2
Or, just remove the optional keywordCONSTRAINTand the symbol/identifer following it, and justADD FOREIGN KEY ...since InnoDB will automatically generate unique symbols for each. (+1)
– Michael - sqlbot
Sep 15 '16 at 4:06
2
2
Or, just remove the optional keyword
CONSTRAINT and the symbol/identifer following it, and just ADD FOREIGN KEY ... since InnoDB will automatically generate unique symbols for each. (+1)– Michael - sqlbot
Sep 15 '16 at 4:06
Or, just remove the optional keyword
CONSTRAINT and the symbol/identifer following it, and just ADD FOREIGN KEY ... since InnoDB will automatically generate unique symbols for each. (+1)– Michael - sqlbot
Sep 15 '16 at 4:06
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f39501899%2fmysql-duplicate-foreign-key-constraint%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
