CODEWITHSUNDEEP
mysql
febici
febici

Reputation: 1

Make mySQL column unique for each foreign key

Let's say I have the following column in my database:

Item:
id int PRIMARY KEY,
name string,
foreign_id FOREIGN KEY

Is there a way without querying the database before insertion each time, that one foreign key cannot contain two rows with the same name?

Upvotes: 0

Views: 77

Answers (3)

Suraj Libi
Suraj Libi

Reputation: 515

As i Understand using FOREIGN KEY (foreign_id) REFERENCES --- will solve it. And make sure that the always make a unique key of table as foreign key for other table.

CREATE TABLE profile
( 
  id int NOT NULL PRIMARY KEY,
  name varchar(50),
  FOREIGN KEY (name)
     REFERENCES member (name) 
     ON DELETE CASCADE
     ON UPDATE CASCADE
) ENGINE=InnoDB ;

Upvotes: 0

Daniele
Daniele

Reputation: 2837

Sure, you want to add an (unique) index for your foreign key column. The SQL command to add that is

ALTER TABLE `mytable`
ADD UNIQUE INDEX `mytable_idx__1` (`foreign_id`);

Upvotes: 1

Fuiba
Fuiba

Reputation: 44

If I understand correctly, you might want to use the UNIQUE constraint:

CREATE TABLE (
    id          INT PRIMARY KEY
  , name        VARCHAR(50) --or whatever you need
  , foreign_id  INT UNIQUE
    FOREIGN KEY (foreign_id) REFERENCES...
);

Upvotes: 0

Related Questions