CODEWITHSUNDEEP
mysqlsql
user725995
user725995

Reputation:

MySQL unique id across multiple tables

I have 2 tables with the following structure:

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.

How can I make the name value unique across multiple tables?

Upvotes: 2

Views: 2241

Answers (2)

dmcnelis
dmcnelis

Reputation: 2923

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.

Upvotes: 1

Augusto
Augusto

Reputation: 29847

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.

Otherwise you can enforce it with triggers, but I don't know how triggers work in mysql.

Upvotes: 1

Related Questions