Mixing
Is using multiple unique constrains on a single table considered bad design?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
3
down vote
favorite
I was looking at PostgreSQL's INSERT INTO .. ON CONFLICT (..) DO UPDATE .. syntax and realized, you cannot do multiple unique constraint checks with it. I mean, you either refer to a composite unique index by the column names ON CONFLICT (Name, Symbol) (if the unique index is defined for these two columns), or you use the primary key. If you define two separate unique indexes for the columns, you can only check for one.
CREATE TABLE student
(Id int primary key, Name varchar(50), Symbol varchar(50),
CONSTRAINT col1_unique UNIQUE (Name),
CONSTRAINT col2_unique UNIQUE (Symbol)
);
INSERT INTO student
(Id, Name, Symbol)
VALUES
(1, 'John', 'J'),
(2, 'David', 'D'),
(3, 'Will', 'W');
INSERT INTO student
(Id, Name, Symbol)
VALUES
(4, 'Jeremy', 'J')
on conflict(Name) DO UPDATE
set Name = 'Jeremy';
Could throw an error, saying J is a duplicate. However, this example is simply a bad design, because the Symbol should be in another table and be connected to the student table via a one to many relationship. Which is why I am wondering, maybe PostgreSQL's on conflict was designed this way, because you can ALWAYS restructure the tables in a way, where there is only a single unique index. Is it true or there is an another reason?
Example fiddle: http://www.sqlfiddle.com/#!17/9c0ce
postgresql database-design merge
edited 3 hours ago
a_horse_with_no_name
36.7k770109
asked 3 hours ago
appl3r
1385
add a comment |Â
up vote
3
down vote
favorite
I was looking at PostgreSQL's INSERT INTO .. ON CONFLICT (..) DO UPDATE .. syntax and realized, you cannot do multiple unique constraint checks with it. I mean, you either refer to a composite unique index by the column names ON CONFLICT (Name, Symbol) (if the unique index is defined for these two columns), or you use the primary key. If you define two separate unique indexes for the columns, you can only check for one.
CREATE TABLE student
(Id int primary key, Name varchar(50), Symbol varchar(50),
CONSTRAINT col1_unique UNIQUE (Name),
CONSTRAINT col2_unique UNIQUE (Symbol)
);
INSERT INTO student
(Id, Name, Symbol)
VALUES
(1, 'John', 'J'),
(2, 'David', 'D'),
(3, 'Will', 'W');
INSERT INTO student
(Id, Name, Symbol)
VALUES
(4, 'Jeremy', 'J')
on conflict(Name) DO UPDATE
set Name = 'Jeremy';
Could throw an error, saying J is a duplicate. However, this example is simply a bad design, because the Symbol should be in another table and be connected to the student table via a one to many relationship. Which is why I am wondering, maybe PostgreSQL's on conflict was designed this way, because you can ALWAYS restructure the tables in a way, where there is only a single unique index. Is it true or there is an another reason?
Example fiddle: http://www.sqlfiddle.com/#!17/9c0ce
postgresql database-design merge
edited 3 hours ago
a_horse_with_no_name
36.7k770109
asked 3 hours ago
appl3r
1385
you can ALWAYS restructure the tables in a way, where there is only a single unique index There are EntityA and EntityB. There are 2 instances of each. Any EntityA instance can combine with any EntityB instance, so there are 2 possible combinations where all instances are used. One of this combinations is realized, and you must store it. Now try to make a scheme where no table with 2 uniques exists with all constraints which prevents illegal data.
– Akina
3 hours ago
@Akina I missed your point. If two entities are connected via a many to many relationship, you create a link table, storing their foreign keys, so there is no table with 2 unique indexes.
– appl3r
3 hours ago
One instance can be used only once, so you must have 2 uniqie indices in a linked table (by each entity separately) to prevent use of instance already used.
– Akina
3 hours ago
add a comment |Â
up vote
3
down vote
favorite
up vote
3
down vote
favorite
I was looking at PostgreSQL's INSERT INTO .. ON CONFLICT (..) DO UPDATE .. syntax and realized, you cannot do multiple unique constraint checks with it. I mean, you either refer to a composite unique index by the column names ON CONFLICT (Name, Symbol) (if the unique index is defined for these two columns), or you use the primary key. If you define two separate unique indexes for the columns, you can only check for one.
CREATE TABLE student
(Id int primary key, Name varchar(50), Symbol varchar(50),
CONSTRAINT col1_unique UNIQUE (Name),
CONSTRAINT col2_unique UNIQUE (Symbol)
);
INSERT INTO student
(Id, Name, Symbol)
VALUES
(1, 'John', 'J'),
(2, 'David', 'D'),
(3, 'Will', 'W');
INSERT INTO student
(Id, Name, Symbol)
VALUES
(4, 'Jeremy', 'J')
on conflict(Name) DO UPDATE
set Name = 'Jeremy';
Could throw an error, saying J is a duplicate. However, this example is simply a bad design, because the Symbol should be in another table and be connected to the student table via a one to many relationship. Which is why I am wondering, maybe PostgreSQL's on conflict was designed this way, because you can ALWAYS restructure the tables in a way, where there is only a single unique index. Is it true or there is an another reason?
Example fiddle: http://www.sqlfiddle.com/#!17/9c0ce
postgresql database-design merge
edited 3 hours ago
a_horse_with_no_name
36.7k770109
asked 3 hours ago
appl3r
1385
I was looking at PostgreSQL's INSERT INTO .. ON CONFLICT (..) DO UPDATE .. syntax and realized, you cannot do multiple unique constraint checks with it. I mean, you either refer to a composite unique index by the column names ON CONFLICT (Name, Symbol) (if the unique index is defined for these two columns), or you use the primary key. If you define two separate unique indexes for the columns, you can only check for one.
CREATE TABLE student
(Id int primary key, Name varchar(50), Symbol varchar(50),
CONSTRAINT col1_unique UNIQUE (Name),
CONSTRAINT col2_unique UNIQUE (Symbol)
);
INSERT INTO student
(Id, Name, Symbol)
VALUES
(1, 'John', 'J'),
(2, 'David', 'D'),
(3, 'Will', 'W');
INSERT INTO student
(Id, Name, Symbol)
VALUES
(4, 'Jeremy', 'J')
on conflict(Name) DO UPDATE
set Name = 'Jeremy';
Could throw an error, saying J is a duplicate. However, this example is simply a bad design, because the Symbol should be in another table and be connected to the student table via a one to many relationship. Which is why I am wondering, maybe PostgreSQL's on conflict was designed this way, because you can ALWAYS restructure the tables in a way, where there is only a single unique index. Is it true or there is an another reason?
Example fiddle: http://www.sqlfiddle.com/#!17/9c0ce
postgresql database-design merge
postgresql database-design merge
edited 3 hours ago
a_horse_with_no_name
36.7k770109
asked 3 hours ago
appl3r
1385
edited 3 hours ago
a_horse_with_no_name
36.7k770109
asked 3 hours ago
appl3r
1385
edited 3 hours ago
a_horse_with_no_name
36.7k770109
edited 3 hours ago
a_horse_with_no_name
36.7k770109
edited 3 hours ago
a_horse_with_no_name
36.7k770109
36.7k770109
asked 3 hours ago
appl3r
1385
asked 3 hours ago
appl3r
1385
asked 3 hours ago
appl3r
1385
1385
you can ALWAYS restructure the tables in a way, where there is only a single unique index There are EntityA and EntityB. There are 2 instances of each. Any EntityA instance can combine with any EntityB instance, so there are 2 possible combinations where all instances are used. One of this combinations is realized, and you must store it. Now try to make a scheme where no table with 2 uniques exists with all constraints which prevents illegal data.
– Akina
3 hours ago
@Akina I missed your point. If two entities are connected via a many to many relationship, you create a link table, storing their foreign keys, so there is no table with 2 unique indexes.
– appl3r
3 hours ago
One instance can be used only once, so you must have 2 uniqie indices in a linked table (by each entity separately) to prevent use of instance already used.
– Akina
3 hours ago
add a comment |Â
you can ALWAYS restructure the tables in a way, where there is only a single unique index There are EntityA and EntityB. There are 2 instances of each. Any EntityA instance can combine with any EntityB instance, so there are 2 possible combinations where all instances are used. One of this combinations is realized, and you must store it. Now try to make a scheme where no table with 2 uniques exists with all constraints which prevents illegal data.
– Akina
3 hours ago
@Akina I missed your point. If two entities are connected via a many to many relationship, you create a link table, storing their foreign keys, so there is no table with 2 unique indexes.
– appl3r
3 hours ago
One instance can be used only once, so you must have 2 uniqie indices in a linked table (by each entity separately) to prevent use of instance already used.
– Akina
3 hours ago
you can ALWAYS restructure the tables in a way, where there is only a single unique index There are EntityA and EntityB. There are 2 instances of each. Any EntityA instance can combine with any EntityB instance, so there are 2 possible combinations where all instances are used. One of this combinations is realized, and you must store it. Now try to make a scheme where no table with 2 uniques exists with all constraints which prevents illegal data.
– Akina
3 hours ago
you can ALWAYS restructure the tables in a way, where there is only a single unique index There are EntityA and EntityB. There are 2 instances of each. Any EntityA instance can combine with any EntityB instance, so there are 2 possible combinations where all instances are used. One of this combinations is realized, and you must store it. Now try to make a scheme where no table with 2 uniques exists with all constraints which prevents illegal data.
– Akina
3 hours ago
@Akina I missed your point. If two entities are connected via a many to many relationship, you create a link table, storing their foreign keys, so there is no table with 2 unique indexes.
– appl3r
3 hours ago
@Akina I missed your point. If two entities are connected via a many to many relationship, you create a link table, storing their foreign keys, so there is no table with 2 unique indexes.
– appl3r
3 hours ago
One instance can be used only once, so you must have 2 uniqie indices in a linked table (by each entity separately) to prevent use of instance already used.
– Akina
3 hours ago
One instance can be used only once, so you must have 2 uniqie indices in a linked table (by each entity separately) to prevent use of instance already used.
– Akina
3 hours ago
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
3
down vote
There is always Sixth, or Domain Key, Normal Form. Here each non-key column becomes it own table. So 3NF table T(Key, Col1, Col2, ..) becomes T1(Key, Col1), T2(Key, Col2) etc. Those new tables which require uniqueness can have it declared.
I think having multiple unique constraints on a table is perfectly OK, however. Take for example a table of countries. This would have, say, an ID, the name, the ISO code, the capital city, and some others. Each of those first four will be unique. Moreover, if we want our system to rely on each being unique I believe we should define unique constraints on each. This enforces truths about the data on which all consumers can rely.
answered 48 mins ago
Michael Green
13.4k82958
Do you think that the postgresql design is only the result of short-sightedness then? Oracle, SQL Server, DB2 and others all provide a way to take into account all the unique keys in such situation (using theMERGEstatement). The pgON CONFLICT DO UPDATEwas only added in 9.5 and is much more limited.
– appl3r
27 mins ago
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
There is always Sixth, or Domain Key, Normal Form. Here each non-key column becomes it own table. So 3NF table T(Key, Col1, Col2, ..) becomes T1(Key, Col1), T2(Key, Col2) etc. Those new tables which require uniqueness can have it declared.
I think having multiple unique constraints on a table is perfectly OK, however. Take for example a table of countries. This would have, say, an ID, the name, the ISO code, the capital city, and some others. Each of those first four will be unique. Moreover, if we want our system to rely on each being unique I believe we should define unique constraints on each. This enforces truths about the data on which all consumers can rely.
answered 48 mins ago
Michael Green
13.4k82958
Do you think that the postgresql design is only the result of short-sightedness then? Oracle, SQL Server, DB2 and others all provide a way to take into account all the unique keys in such situation (using theMERGEstatement). The pgON CONFLICT DO UPDATEwas only added in 9.5 and is much more limited.
– appl3r
27 mins ago
add a comment |Â
up vote
3
down vote
There is always Sixth, or Domain Key, Normal Form. Here each non-key column becomes it own table. So 3NF table T(Key, Col1, Col2, ..) becomes T1(Key, Col1), T2(Key, Col2) etc. Those new tables which require uniqueness can have it declared.
I think having multiple unique constraints on a table is perfectly OK, however. Take for example a table of countries. This would have, say, an ID, the name, the ISO code, the capital city, and some others. Each of those first four will be unique. Moreover, if we want our system to rely on each being unique I believe we should define unique constraints on each. This enforces truths about the data on which all consumers can rely.
answered 48 mins ago
Michael Green
13.4k82958
Do you think that the postgresql design is only the result of short-sightedness then? Oracle, SQL Server, DB2 and others all provide a way to take into account all the unique keys in such situation (using theMERGEstatement). The pgON CONFLICT DO UPDATEwas only added in 9.5 and is much more limited.
– appl3r
27 mins ago
add a comment |Â
up vote
3
down vote
up vote
3
down vote
There is always Sixth, or Domain Key, Normal Form. Here each non-key column becomes it own table. So 3NF table T(Key, Col1, Col2, ..) becomes T1(Key, Col1), T2(Key, Col2) etc. Those new tables which require uniqueness can have it declared.
I think having multiple unique constraints on a table is perfectly OK, however. Take for example a table of countries. This would have, say, an ID, the name, the ISO code, the capital city, and some others. Each of those first four will be unique. Moreover, if we want our system to rely on each being unique I believe we should define unique constraints on each. This enforces truths about the data on which all consumers can rely.
answered 48 mins ago
Michael Green
13.4k82958
There is always Sixth, or Domain Key, Normal Form. Here each non-key column becomes it own table. So 3NF table T(Key, Col1, Col2, ..) becomes T1(Key, Col1), T2(Key, Col2) etc. Those new tables which require uniqueness can have it declared.
I think having multiple unique constraints on a table is perfectly OK, however. Take for example a table of countries. This would have, say, an ID, the name, the ISO code, the capital city, and some others. Each of those first four will be unique. Moreover, if we want our system to rely on each being unique I believe we should define unique constraints on each. This enforces truths about the data on which all consumers can rely.
answered 48 mins ago
Michael Green
13.4k82958
answered 48 mins ago
Michael Green
13.4k82958
answered 48 mins ago
Michael Green
13.4k82958
answered 48 mins ago
Michael Green
13.4k82958
13.4k82958
Do you think that the postgresql design is only the result of short-sightedness then? Oracle, SQL Server, DB2 and others all provide a way to take into account all the unique keys in such situation (using theMERGEstatement). The pgON CONFLICT DO UPDATEwas only added in 9.5 and is much more limited.
– appl3r
27 mins ago
add a comment |Â
Do you think that the postgresql design is only the result of short-sightedness then? Oracle, SQL Server, DB2 and others all provide a way to take into account all the unique keys in such situation (using theMERGEstatement). The pgON CONFLICT DO UPDATEwas only added in 9.5 and is much more limited.
– appl3r
27 mins ago
Do you think that the postgresql design is only the result of short-sightedness then? Oracle, SQL Server, DB2 and others all provide a way to take into account all the unique keys in such situation (using the
MERGE statement). The pg ON CONFLICT DO UPDATE was only added in 9.5 and is much more limited.– appl3r
27 mins ago
Do you think that the postgresql design is only the result of short-sightedness then? Oracle, SQL Server, DB2 and others all provide a way to take into account all the unique keys in such situation (using the
MERGE statement). The pg ON CONFLICT DO UPDATE was only added in 9.5 and is much more limited.– appl3r
27 mins ago
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f219149%2fis-using-multiple-unique-constrains-on-a-single-table-considered-bad-design%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
you can ALWAYS restructure the tables in a way, where there is only a single unique index There are EntityA and EntityB. There are 2 instances of each. Any EntityA instance can combine with any EntityB instance, so there are 2 possible combinations where all instances are used. One of this combinations is realized, and you must store it. Now try to make a scheme where no table with 2 uniques exists with all constraints which prevents illegal data.
– Akina
3 hours ago
@Akina I missed your point. If two entities are connected via a many to many relationship, you create a link table, storing their foreign keys, so there is no table with 2 unique indexes.
– appl3r
3 hours ago
One instance can be used only once, so you must have 2 uniqie indices in a linked table (by each entity separately) to prevent use of instance already used.
– Akina
3 hours ago