Get exclusive CAP network offers from top brands

View CAP Offers

MySQL Expert help needed

kwblue asked 3 years ago
Hey guys,

I am attempting to do something that is quite simple in SQL Server, but I cannot figure out how to do it in MySQL.

I have 2 columns in my database that I want to use as Primary Keys. For example, the combination of the 2 keys would be the primary…. That way I could have:

Key1 = 1, Key2 = 2
Key1 = 1, Key2 = 3

However, I could NOT have:

Key1 = 1, Key2 = 2
Key1 = 1, Key2 = 2

Know what I mean?? This would help tremendously, but I can’t figure it out!

Thanks in advance!

6 Answers
TheGooner answered 3 years ago
OK.

Now while I’ve been programming for 15 years in a preious life – I’ve only taken up MYSQL in the last 6 months – and only done a coulpe of things …

So I’ll give you my answer – but be warned I might still be talking rubbish.
:laugh:

As I understand it PRIMARY keys must be unique (otherwise the couldn’t be found on a key search) – so given the base of your discussion you sohuld consider the following.

It sounds to me like you actually have one REAL key which is a concatenation of both sub keys (key1 and key2).

You should create a UNIQUE PRMIARY key = key3 which is a combination of key1 and key2

Set up Key1 and Key2 as indexed fields. This should enable you to quickly search your database along these lines too. In fact reading the between the line it sounds like you search on these fields more often?

Your programme will need to maintain integrity (whenever Key1 or KEy2 are changed then KEy3 is aletered too.



I THINK that should work for you – if not gimme a little more detail and we should be able to work out a programmatic solution.
:thumbsup:

kwblue answered 3 years ago
I’ll look into that…

I will not need keys to match up, however…

The problem is that there are slots on different casinos with the same name. Since the database was originally set up with the slotname as the primary key – this poses a problem (unless, of course, I prefix the slotname or something similar.)

So, I want the Casino ID AND slotname tied together as one key so that I do not have a problem. Know what I mean.

I may just re-structure the whole stupid database <span title=” title=”” class=”bbcode_smiley” /> Might be the best way out of this.

Thanks.

*Edit* I think it is about time for kwblueDB V2.1 <span title=” title=”” class=”bbcode_smiley” />

TheGooner answered 3 years ago
Hi,

Don’t forget that you can have indexed fields that search quickly that are NOT primary keys.

A good example is my own tipsters database where the PRIMARY key is actually a quite useless field that simply has the tip id.

BUt I have indexed fields for home side, away side, competition, match date and tipster … which allows me to pull out all sorts of lists.

It sounds like neither key field that you mentioned actually need to be primary keys at all – in fact you could adopt the same method I have above and just give each record an arbitary “slot number”.

(or have I gone way off base here?)

kwblue answered 3 years ago
No – you are exactly right..

My V2.0 of my database uses what I call UniqueID’s for all items (auto-increment integers)… Like, each casino has a uniqueid… each bonus has a uniqueid and is tied to the casino’s unique id.. and so on..

However, I created the slots database quite a while ago and in order to retro-fit – it would take some doing. I think it is that time because I am running into these type of issues as I add new slots to the database <span title=” title=”” class=”bbcode_smiley” />

Like I said – time for V2.1

Goldfinger answered 3 years ago
Sounds as if you need to redesign it. If you want something that´s unique but just isn´t then there was a slip up in the db design. I guess casinos should be your primary key as they would always be unique. The good thing though is that with some sweat you should be able to rework your backend in a way that it returns queries that will run with front end as it is at the moment.

I.e. you won´t have to redesign your urls to match your query strings etcetera etcetera.

kwblue answered 3 years ago
On the slots end of things… I’ll probably have a unique identifier (integer) for each casino game. That would be a better way to go (IMO)

The whole system is class-based php, so it will be easy to change the queries in the class module. The framework (html pages) would not be affected except for the output.

The backend admin will be more work <span title=” title=”” class=”bbcode_smiley” />