1

I have a table, say:

CREATE TABLE test ( id SERIAL UNIQUE, first integer NOT NULL, second integer NOT NULL, data text NOT NULL );

How do I enforce a check such that the second column is ‘unique’ with respect to the first column, but duplicate values are allowed? In other words, only allow one value in the ‘second’ for each of the ‘first’ column values. Once a value has been inserted into column 'first', only additional values that match the existing (first, second) column combination can be inserted again.

For example, given:

INSERT INTO test (first, second, data) VALUES (1, 2, 'test');

A constraint violation for:

INSERT INTO test (first, second, data) VALUES (1, 3, 'testing');

would occur, but

INSERT INTO test (first, second, data) VALUES (1, 2, 'testing');

or

INSERT INTO test (first, second, data) VALUES (2, 1, 'testing');

or

INSERT INTO test (first, second, data) VALUES (2, 2, 'testing');

would succeed.

Improve this question
7
  • You mean combination? Make Composite Primary key.. Like this? Commented Dec 12, 2018 at 1:19
  • @dwir182 Yes, I think that is what I am looking for. I tried to simplify it, but what if I have a separate ID column that I was planning on making the Primary key? Commented Dec 12, 2018 at 1:27
  • You could change my fiddle to what you expected and elaborate more info in your question.. Commented Dec 12, 2018 at 1:32
  • @dwir182 Never mind: I think I have it figured out now. If you want to answer I will accept it. Otherwise, I can delete this question. Commented Dec 12, 2018 at 1:32
  • I did modify the question slightly, but I didn't realize you could have both a serial index and a separate primary key. I am heading off to do a little more reading now that you have pointed me in the right direction. Commented Dec 12, 2018 at 1:33

2 Answers 2

Reset to default
1

The problem is that you need two tables. You are trying to carry information from one row to another -- but that is a bad idea. I'm guessing you really have two entities and just don't want to admit it.

So:

CREATE TABLE firstSecond ( firstSecondId SERIAL PRIMARY KEY, first integer NOT NULL, second integer NOT NULL, constraint unq_firstSecond_first_second unique (first, second) ); CREATE TABLE test ( testId SERIAL PRIMARY KEY, firstSecondId int not null references firstSecond(firstSecondId), data text NOT NULL );

These tables have the semantics you want.

Of course, you can also use a composite primary key for the first table:

CREATE TABLE firstSecond ( first integer NOT NULL, second integer NOT NULL, primary key (first, second) ); CREATE TABLE test ( testId SERIAL PRIMARY KEY, first int not null, second int not null, data text NOT NULL, foreign key (first, second) references firstSecond(first, second) );
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

OK, fair enough: you're probably right. I think I'm going to have to look at this tomorrow as I'm getting pretty tired and I have to go help someone with an unrelated task anyway. It would have made the view queries (which are common) use less joins and the insert/update (not common: fairly static data) simpler.
0

Composite Primary Key if you are looking for combination.

Definition :

Combination of two or more columns in a table that can be used to uniquely identify each row in the table.

The demo = Demo

Improve this answer

8 Comments

Actually, now that I have tried it a little more carefully, this is the opposite of what I want. This will fail a combination of 1, 2 but succeed on 1, 3.
What combination do you want? More column means more combination to do.. Like this?
This is what I am having difficulty finding documentation on. Once a value has been inserted into column 'first', only additional values that match the existing (first, second) column combination can be inserted again. It should allow different first values to also be inserted.
Sounds like you need to use 2 tables: (first, second) in tableA, unique for (first, second) then TableB had foreign key back to tableA for reference to first & second, (tableB can have many rows pointing to any unique first & second)
@Rob you mean this?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.