0

I have a table Tags which has 2 columns:

name VARCHAR(50)
group_id INT

The combination on both cannot be repeated so I use a composite key to make sure that the combination of name and group_id cannot be used 2 times.

But since the name is a varchar column, it is not a very good option for querying the database, so if I use an id column which is not a primary key but is an autoincrement, I can search for only one column in the database will be ok?

The table will be like this:

name VARCHAR(50) PRIMARY KEY,
group_id INT PRIMARY KEY
id autoincrement NOT NULL

I never seen this before and it looks like a solution, but I really need other point of view before applying this solution.

I have to import the tags from a file and those tags have a many many relation with another table that I'm also importing from the file, just to illustrate the file structure is like this:

enterprises |TagGroup1 |TagGroup2 |...TagGroupN
Google      |t1.1,t1.2 |t2.1,t2.2 |tN.1,tN.2
canonical   |t1.1.1    |t2.1,t2.2 |tN.1,tN.2

given this file I'll explain that a tag belongs to a group and an enterprise has tags so when I import the file I import the group and then create the tags in bulk, them import enterprises but when I need to import the relation between tags and enterprises if I have need the tag numeric id that will force me to insert the tags one by one which is not a good idea at all, but if I had the name and group ID as key I not longer need to wait for the tag's ID...

sorry this is to long and I'm trying to explain my problem but I don't know if I succeeded in making this simple to understand

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Isan Rodriguez Trimiño
  • 101
  • 2
  • 7
  • 1
    How are you choosing your group id? Is it unique by itself or is it the PK to another table? Can you show the relationship between Tags and Group? If you can add the create table statements in your question it would help. It *sounds* like you want a primary key to the Tags table, and then yes using an autoincrement and making that a PK is the right way to go. Just create an index on the name to give you fast searching for that column, but even without it you can still select on name whether its a key or not. – TomC Mar 07 '19 at 02:17
  • yes, group_id is a relation to another table, the thing is that i think i will have some troubles if a want to pass the keys in an standard GET parametter, that's why i'm thinking of using a id autoincrement to do this job.. And i don't have the table, this is in theory before apply the solution.. – Isan Rodriguez Trimiño Mar 07 '19 at 02:28
  • Well you would definitely need a PK rather than using the description as a PK. Not sure what you are using to query the database, but either way you can query on a name or a pk. Not sure what your "standard get" is, what layer are you passing this through? Whatever it is, you will be able to query by key or value - its all standard common patterns that anything supports. – TomC Mar 07 '19 at 02:47
  • 1
    https://stackoverflow.com/questions/14588304/composite-primary-key-vs-additional-id-column – Amir Molaei Mar 07 '19 at 04:43
  • rad thanks for the link. When i mention GET parametter it's that i don't want to have an URL like "index.php?name=some%20very%20long%20and%20weird%20name&group=5" that's why i ask of using an ID but not declaring it as primary key – Isan Rodriguez Trimiño Mar 07 '19 at 16:58
  • Welcome to StackOverflow! It seems that you are asking several questions; the title suggests this question is primarily about a composite key. If you have additional questions, please feel free to create a new question for each one. – bignose Mar 09 '19 at 06:37

1 Answers1

1

[…] so I use a composite key to make sure that the combination of name and group_id cannot be used 2 times.

You are describing a need for a constraint; that doesn't need to be a key at all. When defining a table you can specify a constraint that multiple fields need to be unique together:

CREATE TABLE tag (
    name varchar(50),
    group_id int,
    UNIQUE (name, group_id) );

That way you get the RDBMS enforcing those columns have a unique pair of values on each record, without implying that they are a key for retrieval.

So then you are free to nominate whatever primary key you like. Because you want the id field to be primary key, go for it:

CREATE TABLE tag (
    name varchar(50),
    group_id int,
    id serial NOT NULL,
    UNIQUE (name, group_id),
    PRIMARY KEY (id) );
bignose
  • 30,281
  • 14
  • 77
  • 110