I have 2 tables with the following structure:
我有2个表,具有以下结构:
CREATE TABLE IF NOT EXISTS `car` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(255) NOT NULL ,
`title` VARCHAR(255) NOT NULL
PRIMARY KEY (`id`) ,
UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `book` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(255) NOT NULL ,
`title` VARCHAR(255) NOT NULL
PRIMARY KEY (`id`) ,
UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
ENGINE = InnoDB;
I generating the name column from the title for example: if title: Foo Bar -> name: foo-bar. I'm using this value in the URL. Eariler i had URLs like this /car/foo-bar or /book/foo-bar having same name value in both table wasnt a problem. But i want shorter URLs now: /foo-bar or /foo-bar-2.
我从标题中生成名称列,例如:if title:Foo Bar - > name:foo-bar。我在URL中使用此值。我觉得这个/ car / foo-bar或/ book / foo-bar这两个表中的URL都是一个问题。但我现在想要更短的URL:/ foo-bar或/ foo-bar-2。
How can I make the name value unique across multiple tables?
如何在多个表中使名称值唯一?
2 个解决方案
#1
1
If you're building an OO application, you would put the name in a separate table and add foreign keys from books and cars to "names"... or whatever that table means in your application, for example product.
如果您正在构建一个OO应用程序,您可以将该名称放在一个单独的表中,并将书籍和汽车中的外键添加到“名称”......或者该表在您的应用程序中表示的任何内容,例如产品。
Otherwise you can enforce it with triggers, but I don't know how triggers work in mysql.
否则你可以使用触发器强制执行它,但我不知道mysql中的触发器是如何工作的。
#2
1
If you want to force it to be unique, then you could create a third table called name_reference for example with a prinary key of the combination of the fields name and a field called type. Then you could have book and car have foreign keys to the name_reference.
如果要强制它是唯一的,那么您可以创建一个名为name_reference的第三个表,例如,使用字段名称和名为type的字段组合的原始键。那么你可以让book和car拥有name_reference的外键。
#1
1
If you're building an OO application, you would put the name in a separate table and add foreign keys from books and cars to "names"... or whatever that table means in your application, for example product.
如果您正在构建一个OO应用程序,您可以将该名称放在一个单独的表中,并将书籍和汽车中的外键添加到“名称”......或者该表在您的应用程序中表示的任何内容,例如产品。
Otherwise you can enforce it with triggers, but I don't know how triggers work in mysql.
否则你可以使用触发器强制执行它,但我不知道mysql中的触发器是如何工作的。
#2
1
If you want to force it to be unique, then you could create a third table called name_reference for example with a prinary key of the combination of the fields name and a field called type. Then you could have book and car have foreign keys to the name_reference.
如果要强制它是唯一的,那么您可以创建一个名为name_reference的第三个表,例如,使用字段名称和名为type的字段组合的原始键。那么你可以让book和car拥有name_reference的外键。