Can I have 2 unique columns in the same table?

Question

I have 2 tables:

• roomtypes[id(PK),name,maxAdults...]
• features(example: Internet in room, satelite tv)

Can both id and name field be unique in the same table in mysql MYISAM?

If the above is posible, I am thinking of changing the table to:

• features[id(PK),name,roomtypeID] ==> features[id(PK),name,roomtypeNAME]

...because it is helping me not to do extra querying in presentation for features because the front end users can't handle with IDs.

Answer 1

Of course, you can make one of them PRIMARY and one UNIQUE. Or both UNIQUE. Or one PRIMARY and four UNIQUEs, if you like

Answer 2

I would hope so otherwise MySQL is not compliant with the SQL standard. You can only have one primary key but you can mark other columns as unique.

In SQL, this is achieved with:

create table tbl (
    colpk char(10) primary key,
    coluniq char(10) unique,
    colother char(10)
);

There are other ways to do it (particularly with multi-part keys) but this is a simple solution.

Answer 3

Yes, you can define UNIQUE constraints to columns other than the primary key in order to ensure the data is unique between rows. This means that the value can only exist in that column once - any attempts to add duplicates will result in a unique constraint violation error.

I am thinking of changing the FEATURES table to features[id(PK), name, roomtypeNAME] because it is helping me not to do extra querying in presentation for features because the front end users can't handle with IDs.

There's two problems:

1. A unique constraint on the ROOM_TYPE_NAME wouldn't work - you'll have multiple instances of a given room type, and a unique constraint is designed to stop that.
2. Because of not using a foreign key to the ROOM_TYPES table, you risk getting values like "Double", "double", "dOUBle"

I recommend sticking with your original design for sake of your data; your application is what translates a room type into its respective ROOM_TYPE record while the UI makes it presentable.

Answer 4

Yes you can.

Also keep in mind that MySQL allow NULL values in unique columns, whereas a column that is a primary key cannot have a NULL value.

Answer 5

Thanks again for very helpful answers.

1 roomType may have many features

1 feature may be assigned to many roomTypes

So what type of relationship do i have? M:N ?

If yes the solution I see is changing table structure to roomTypes[id,...,featuresIDs] features[id(PK),name,roomtypeIDs] multiple roomTypesIDs separated with comma?

Answer 6

1 RoomType may have many Features
1 Feature may be assigned to many RoomTypes
So what type of relationship do i have? M:N ?

You have there a many-to-many relationship, which has to be represented by an extra table.
That relationship table will have 2 fields: the PK of RoomTypes and the PK of Features.
The PK of the relationship table will be made of those 2 fields. If that's usefull, you can add extra fields like the Quantity.

I would like to encourage you to read about database Normalization, which is he process of creating a correct design for a relational database. You can Google for that, or look eventually here (there are plenty of books/web pages on this)