Primary Key vs Unique Constraint
A user asked whether the only real difference between a PRIMARY KEY and a UNIQUE constraint is that foreign keys can reference only primary keys. The thread answered the question: a unique constraint can be referenced by a foreign key, but you must name the parent column explicitly (REFERENCES parent(col1)), otherwise you get error -297. Otherwise the two are equivalent to the engine; differences are that a table can have many unique keys but only one primary key, a primary key allows no NULLs while a unique constraint permits one, Enterprise Replication requires a primary key, and some external tools (older SQL Server/Access links) reportedly needed a primary key.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is the primary difference between primary key and unique constraint that primary keys can be used with foreign keys and unique constraints can't? Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu> ============================== SLES 11x64 & IDS 11.50.FC6 "Don't document the problem, fix it." - Atli Björgvin Oddsson
You can use a unique constraint on a parent table if you want to create a
foreign key on a child table.
But for that you must include the column(s) of the parent table that you're
referencing.
With an example....
Having this:
create table parent
(
col1 integer
);
create table child
(
col1 integer,
col2 integer
);
alter table parent add constraint unique (col1);
This will NOT work:
alter table child add constraint foreign key (col2) references parent;
but this WILL work:
alter table child add constraint foreign key (col2) references parent(col1);
The description of error -297 is perfectly clear:
-297 Cannot find unique constraint or primary key on referenced
table <table_name>.
The database server cannot locate the referenced constraint in the
sysconstraints system catalog table, and the referenced constraint
was not created in the same ALTER TABLE statement as the referencing
constraint. The referenced constraint might not exist, or a foreign key
might refer to a table that has a unique constraint but not a
primary-key constraint.
Check that you have entered a valid column name with the appropriate
constraints that are associated with it. If the referenced table has a
unique constraint but no primary key, you must use the following form of
the REFERENCES clause:
REFERENCES table_name (column_name)
Valid constraint columns indicate an internal error. If the error
recurs, note all circumstances and contact IBM Technical Support.
Regards.
On Thu, Jun 24, 2010 at 6:47 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote:
> Is the primary difference between primary key and unique constraint that
> primary keys can be used with foreign keys and unique constraints can't?
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu>
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e6d7ee9644e1a90489ca72db
Ok,
I was trying to understand what benefits one type of control over the other.
Is primary key more efficient than unique constraint or are they equal as far
as the engine is concerned.
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6
"Don't document the problem, fix it."
- Atli Björgvin Oddsson
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando
Nunes
Sent: Thursday, June 24, 2010 2:02 PM
To: ids@iiug.org
Subject: Re: Primary Key vs Unique Constraint [20479]
You can use a unique constraint on a parent table if you want to create a
foreign key on a child table.
But for that you must include the column(s) of the parent table that you're
referencing.
With an example....
Having this:
create table parent
(
col1 integer
);
create table child
(
col1 integer,
col2 integer
);
alter table parent add constraint unique (col1);
This will NOT work:
alter table child add constraint foreign key (col2) references parent;
but this WILL work:
alter table child add constraint foreign key (col2) references parent(col1);
The description of error -297 is perfectly clear:
-297 Cannot find unique constraint or primary key on referenced table
<table_name>.
The database server cannot locate the referenced constraint in the
sysconstraints system catalog table, and the referenced constraint was not
created in the same ALTER TABLE statement as the referencing constraint. The
referenced constraint might not exist, or a foreign key might refer to a table
that has a unique constraint but not a primary-key constraint.
Check that you have entered a valid column name with the appropriate
constraints that are associated with it. If the referenced table has a unique
constraint but no primary key, you must use the following form of the
REFERENCES clause:
REFERENCES table_name (column_name)
Valid constraint columns indicate an internal error. If the error recurs, note
all circumstances and contact IBM Technical Support.
Regards.
On Thu, Jun 24, 2010 at 6:47 PM, Wyza, Jonathon
<wyzaj@bethelcollege.edu>wrote:
> Is the primary difference between primary key and unique constraint
> that primary keys can be used with foreign keys and unique constraints can't?
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu
> >
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e6d7ee9644e1a90489ca72db
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
All the same. It's just that you can have more than one unique key buy only
one primary key.
Art
On Jun 24, 2010 2:11 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu> wrote:
Ok,
I was trying to understand what benefits one type of control over the other.
Is primary key more efficient than unique constraint or are they equal as
far
as the engine is concerned.
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6
"Don't document the problem, fix it."
- Atli Björgvin Oddsson
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Fernando
Nunes
Sent: Thursday, June 24, 2010 2:02 PM
To: ids@iiug.org
Subject: Re: Primary Key vs Unique Constraint [20479]
You can use a unique constraint on a parent table if you want to create a
foreign key on a child table.
But for that you must include the column(s) of the parent table that you're
referencing.
With an example....
Having this:
create table parent
(
col1 integer
);
create table child
(
col1 integer,
col2 integer
);
alter table parent add constraint unique (col1);
This will NOT work:
alter table child add constraint foreign key (col2) references parent;
but this WILL work:
alter table child add constraint foreign key (col2) references parent(col1);
The description of error -297 is perfectly clear:
-297 Cannot find unique constraint or primary key on referenced table
<table_name>.
The database server cannot locate the referenced constraint in the
sysconstraints system catalog table, and the referenced constraint was not
created in the same ALTER TABLE statement as the referencing constraint. The
referenced constraint might not exist, or a foreign key might refer to a
table
that has a unique constraint but not a primary-key constraint.
Check that you have entered a valid column name with the appropriate
constraints that are associated with it. If the referenced table has a
unique
constraint but no primary key, you must use the following form of the
REFERENCES clause:
REFERENCES table_name (column_name)
Valid constraint columns indicate an internal error. If the error recurs,
note
all circumstances and contact IBM Technical Support.
Regards.
On Thu, Jun 24, 2010 at 6:47 PM, Wyza, Jonathon
<wyzaj@bethelcollege.edu>wrote:
> Is the primary difference between primary key and unique constraint
> that primary keys can be used with foreign keys and unique constraints
can't?
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu
> >
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e6d7ee9644e1a90489ca72db
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
--000e0cd722e2e72ec10489caa724
Used to SQL server and Access would not link to Informix tables if it did not
have a primary key. Constraints did not work. That was 5 years ago or so, it
may not be that way anymore.
Thank you,
Jim Goldrick
Judson University
573-332-7739
http://www.judsonu.edu
jgoldrick@judsonu.edu
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Thursday, June 24, 2010 1:17 PM
To: ids@iiug.org
Subject: Re: RE: Primary Key vs Unique Constraint [20481]
All the same. It's just that you can have more than one unique key buy only
one primary key.
Art
On Jun 24, 2010 2:11 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu> wrote:
Ok,
I was trying to understand what benefits one type of control over the other.
Is primary key more efficient than unique constraint or are they equal as
far
as the engine is concerned.
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6
"Don't document the problem, fix it."
- Atli Björgvin Oddsson
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Fernando
Nunes
Sent: Thursday, June 24, 2010 2:02 PM
To: ids@iiug.org
Subject: Re: Primary Key vs Unique Constraint [20479]
You can use a unique constraint on a parent table if you want to create a
foreign key on a child table.
But for that you must include the column(s) of the parent table that you're
referencing.
With an example....
Having this:
create table parent
(
col1 integer
);
create table child
(
col1 integer,
col2 integer
);
alter table parent add constraint unique (col1);
This will NOT work:
alter table child add constraint foreign key (col2) references parent;
but this WILL work:
alter table child add constraint foreign key (col2) references parent(col1);
The description of error -297 is perfectly clear:
-297 Cannot find unique constraint or primary key on referenced table
<table_name>.
The database server cannot locate the referenced constraint in the
sysconstraints system catalog table, and the referenced constraint was not
created in the same ALTER TABLE statement as the referencing constraint. The
referenced constraint might not exist, or a foreign key might refer to a
table
that has a unique constraint but not a primary-key constraint.
Check that you have entered a valid column name with the appropriate
constraints that are associated with it. If the referenced table has a
unique
constraint but no primary key, you must use the following form of the
REFERENCES clause:
REFERENCES table_name (column_name)
Valid constraint columns indicate an internal error. If the error recurs,
note
all circumstances and contact IBM Technical Support.
Regards.
On Thu, Jun 24, 2010 at 6:47 PM, Wyza, Jonathon
<wyzaj@bethelcollege.edu>wrote:
> Is the primary difference between primary key and unique constraint
> that primary keys can be used with foreign keys and unique constraints
can't?
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu
> >
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e6d7ee9644e1a90489ca72db
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
--000e0cd722e2e72ec10489caa724
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
A primary key does not allow any nulls
An unique constraint allows one null.
Zev Berezin
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Wyza,
Jonathon
Sent: Thursday, June 24, 2010 2:12 PM
To: ids@iiug.org
Subject: RE: Primary Key vs Unique Constraint [20480]
Ok,
I was trying to understand what benefits one type of control over the other.
Is primary key more efficient than unique constraint or are they equal as far
as the engine is concerned.
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6
"Don't document the problem, fix it."
- Atli Björgvin Oddsson
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando
Nunes
Sent: Thursday, June 24, 2010 2:02 PM
To: ids@iiug.org
Subject: Re: Primary Key vs Unique Constraint [20479]
You can use a unique constraint on a parent table if you want to create a
foreign key on a child table.
But for that you must include the column(s) of the parent table that you're
referencing.
With an example....
Having this:
create table parent
(
col1 integer
);
create table child
(
col1 integer,
col2 integer
);
alter table parent add constraint unique (col1);
This will NOT work:
alter table child add constraint foreign key (col2) references parent;
but this WILL work:
alter table child add constraint foreign key (col2) references parent(col1);
The description of error -297 is perfectly clear:
-297 Cannot find unique constraint or primary key on referenced table
<table_name>.
The database server cannot locate the referenced constraint in the
sysconstraints system catalog table, and the referenced constraint was not
created in the same ALTER TABLE statement as the referencing constraint. The
referenced constraint might not exist, or a foreign key might refer to a table
that has a unique constraint but not a primary-key constraint.
Check that you have entered a valid column name with the appropriate
constraints that are associated with it. If the referenced table has a unique
constraint but no primary key, you must use the following form of the
REFERENCES clause:
REFERENCES table_name (column_name)
Valid constraint columns indicate an internal error. If the error recurs, note
all circumstances and contact IBM Technical Support.
Regards.
On Thu, Jun 24, 2010 at 6:47 PM, Wyza, Jonathon
<wyzaj@bethelcollege.edu>wrote:
> Is the primary difference between primary key and unique constraint
> that primary keys can be used with foreign keys and unique constraints
can't?
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu
> >
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e6d7ee9644e1a90489ca72db
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
And a primary key is required for Enterprise Replication.
James
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Zev
Berezin
Sent: Friday, June 25, 2010 8:29 AM
To: ids@iiug.org
Subject: RE: Primary Key vs Unique Constraint [20483]
A primary key does not allow any nulls
An unique constraint allows one null.
Zev Berezin
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Wyza,
Jonathon
Sent: Thursday, June 24, 2010 2:12 PM
To: ids@iiug.org
Subject: RE: Primary Key vs Unique Constraint [20480]
Ok,
I was trying to understand what benefits one type of control over the other.
Is primary key more efficient than unique constraint or are they equal as far
as the engine is concerned.
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6
"Don't document the problem, fix it."
- Atli Björgvin Oddsson
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando
Nunes
Sent: Thursday, June 24, 2010 2:02 PM
To: ids@iiug.org
Subject: Re: Primary Key vs Unique Constraint [20479]
You can use a unique constraint on a parent table if you want to create a
foreign key on a child table.
But for that you must include the column(s) of the parent table that you're
referencing.
With an example....
Having this:
create table parent
(
col1 integer
);
create table child
(
col1 integer,
col2 integer
);
alter table parent add constraint unique (col1);
This will NOT work:
alter table child add constraint foreign key (col2) references parent;
but this WILL work:
alter table child add constraint foreign key (col2) references parent(col1);
The description of error -297 is perfectly clear:
-297 Cannot find unique constraint or primary key on referenced table
<table_name>.
The database server cannot locate the referenced constraint in the
sysconstraints system catalog table, and the referenced constraint was not
created in the same ALTER TABLE statement as the referencing constraint. The
referenced constraint might not exist, or a foreign key might refer to a table
that has a unique constraint but not a primary-key constraint.
Check that you have entered a valid column name with the appropriate
constraints that are associated with it. If the referenced table has a unique
constraint but no primary key, you must use the following form of the
REFERENCES clause:
REFERENCES table_name (column_name)
Valid constraint columns indicate an internal error. If the error recurs, note
all circumstances and contact IBM Technical Support.
Regards.
On Thu, Jun 24, 2010 at 6:47 PM, Wyza, Jonathon
<wyzaj@bethelcollege.edu>wrote:
> Is the primary difference between primary key and unique constraint
> that primary keys can be used with foreign keys and unique constraints
can't?
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu
> >
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e6d7ee9644e1a90489ca72db
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
DISCLAIMER:
This email contains confidential information and may be legally privileged. If
you are not the intended recipient or have received this email in error,
please notify the sender immediately and destroy this email.
You may not use, disclose or copy this email or its attachments in any way.
Any opinions expressed in this email are those of the author and are not
necessarily those of the Fonterra Co-operative Group.
http://www.fonterra.com/