CODEWITHSUNDEEP
phpmysqlsqlcs50
menan
menan

Reputation: 195

mysql making two categories unique to each other

I have a row called user_id and symbol. I want to make it so each user_id can only have a certain symbol once.

This is the line I wrote to do this

ALTER TABLE  `portfolio` ADD UNIQUE (
`user_id` ,
`symbol`
);

However this makes it so I also can't have a user_id more then once. So if I add a column with a user_id = 21 and a symbol = AAA , I cant add another column with a user_id = 21 and a symbol = BBB.

ny ideas

Upvotes: 1

Views: 25

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The following constraint does exactly what you want: it makes sure that each user/symbol combination only appears once:

ALTER TABLE portfolio ADD UNIQUE (user_id, symbol);

A good practice when adding constraints is to give them meaningful names:

ALTER TABLE portfolio
    ADD CONSTRAINT unq_portfolio_userid_symbol
        UNIQUE(user_id, symbol);

That way, when the constraint is violated, you know which constraint is violated. If you cannot add the user with another symbol, then perhaps user_id (or symbol) is declared as the primary key on the table or unique itself or as part of another unique column.

Upvotes: 2

Jim Macaulay
Jim Macaulay

Reputation: 5141

Hi Menan
In this case you can use composite primary key

ALTER TABLE portfolio add 
constraint pkc_portfolio primary key (id, code);

Your combination of id and code will not be repeated

Upvotes: 0

Related Questions