Check constraints

Hello,

I have a table called Company_Locations. There is a field named Is_Main. For
any given COMPANYID, i want that only one Location can have the IS_MAIN
checked. How can i do this?

In ORACLE or SQL*Server, i would simply throw a check constraint on the
table, or i could use a trigger. However, as far as i know, neither of these
exists in Access. So how would i be able to do this?

Thanks in advance,

- Aaron.

Well, here's one way. However, it only checks that one location is
designated as the main location. It does NOT require that at least one
location MUST be the main.

Anyway, your IS_MAIN field must be a number field (integer or byte is best).
This will not work with a Yes/No

1) Set the Default Value of IS_MAIN to null
2) Set the Validation rule to 1
3) Create a Unique (compound) Index on COMPANYID and IS_MAIN

The validation rule ensures that only a 1 or a null can be entered into the
field. The unique index ensures that only one IS_MAIN per company can have
a 1.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Aaron Abdis" wrote in message
...
Hello,

I have a table called Company_Locations. There is a field named Is_Main.
For
any given COMPANYID, i want that only one Location can have the IS_MAIN
checked. How can i do this?

In ORACLE or SQL*Server, i would simply throw a check constraint on the
table, or i could use a trigger. However, as far as i know, neither of
these
exists in Access. So how would i be able to do this?

Thanks in advance,

- Aaron.

Ah... okay, i think i see. Then multiple additional locations would not
break the index because NULL != NULL, correct?

"Roger Carlson" wrote in message
...
Well, here's one way. However, it only checks that one location is
designated as the main location. It does NOT require that at least one
location MUST be the main.

Anyway, your IS_MAIN field must be a number field (integer or byte is
best).
This will not work with a Yes/No

1) Set the Default Value of IS_MAIN to null
2) Set the Validation rule to 1
3) Create a Unique (compound) Index on COMPANYID and IS_MAIN

The validation rule ensures that only a 1 or a null can be entered into
the
field. The unique index ensures that only one IS_MAIN per company can
have
a 1.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Aaron Abdis" wrote in message
...
Hello,

I have a table called Company_Locations. There is a field named Is_Main.
For
any given COMPANYID, i want that only one Location can have the IS_MAIN
checked. How can i do this?

In ORACLE or SQL*Server, i would simply throw a check constraint on the
table, or i could use a trigger. However, as far as i know, neither of
these
exists in Access. So how would i be able to do this?

Thanks in advance,

- Aaron.

(NULL != NULL) is False - not True.

HTH,
TC


"Aaron Abdis" wrote in message
...
Ah... okay, i think i see. Then multiple additional locations would not
break the index because NULL != NULL, correct?

"Roger Carlson" wrote in message
...
Well, here's one way. However, it only checks that one location is
designated as the main location. It does NOT require that at least one
location MUST be the main.

Anyway, your IS_MAIN field must be a number field (integer or byte is
best).
This will not work with a Yes/No

1) Set the Default Value of IS_MAIN to null
2) Set the Validation rule to 1
3) Create a Unique (compound) Index on COMPANYID and IS_MAIN

The validation rule ensures that only a 1 or a null can be entered into
the
field. The unique index ensures that only one IS_MAIN per company can
have
a 1.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Aaron Abdis" wrote in message
...
Hello,

I have a table called Company_Locations. There is a field named
Is_Main.
For
any given COMPANYID, i want that only one Location can have the
IS_MAIN
checked. How can i do this?

In ORACLE or SQL*Server, i would simply throw a check constraint on
the
table, or i could use a trigger. However, as far as i know, neither of
these
exists in Access. So how would i be able to do this?

Thanks in advance,

- Aaron.

Actually, in VBA, Null Null is neither True nor False. It is Null. Try
the following in the Immediate window ...

? nullnull
Null

Roger's suggestion will, as far as I can see, work with a Jet database. I
believe it would break if the database were ever moved to SQL Server,
because of the different way in which the two database engines handle Null
values in unique (not primary) indexes.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"TC" wrote in message
...
(NULL != NULL) is False - not True.

HTH,
TC


"Aaron Abdis" wrote in message
...
Ah... okay, i think i see. Then multiple additional locations would not
break the index because NULL != NULL, correct?

"Roger Carlson" wrote in message
...
Well, here's one way. However, it only checks that one location is
designated as the main location. It does NOT require that at least
one
location MUST be the main.

Anyway, your IS_MAIN field must be a number field (integer or byte is
best).
This will not work with a Yes/No

1) Set the Default Value of IS_MAIN to null
2) Set the Validation rule to 1
3) Create a Unique (compound) Index on COMPANYID and IS_MAIN

The validation rule ensures that only a 1 or a null can be entered
into
the
field. The unique index ensures that only one IS_MAIN per company can
have
a 1.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Aaron Abdis" wrote in message
...
Hello,

I have a table called Company_Locations. There is a field named
Is_Main.
For
any given COMPANYID, i want that only one Location can have the
IS_MAIN
checked. How can i do this?

In ORACLE or SQL*Server, i would simply throw a check constraint on
the
table, or i could use a trigger. However, as far as i know, neither
of
these
exists in Access. So how would i be able to do this?

Thanks in advance,

- Aaron.

Yes. It is true that Null does not equal Null, even though, as TC mentions,
it won't evaluate that way in an expression. However, a Unique Index in
Access allows multiple Null values.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


"Aaron Abdis" wrote in message
...
Ah... okay, i think i see. Then multiple additional locations would not
break the index because NULL != NULL, correct?

"Roger Carlson" wrote in message
...
Well, here's one way. However, it only checks that one location is
designated as the main location. It does NOT require that at least one
location MUST be the main.

Anyway, your IS_MAIN field must be a number field (integer or byte is
best).
This will not work with a Yes/No

1) Set the Default Value of IS_MAIN to null
2) Set the Validation rule to 1
3) Create a Unique (compound) Index on COMPANYID and IS_MAIN

The validation rule ensures that only a 1 or a null can be entered into
the
field. The unique index ensures that only one IS_MAIN per company can
have
a 1.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Aaron Abdis" wrote in message
...
Hello,

I have a table called Company_Locations. There is a field named
Is_Main.
For
any given COMPANYID, i want that only one Location can have the
IS_MAIN
checked. How can i do this?

In ORACLE or SQL*Server, i would simply throw a check constraint on
the
table, or i could use a trigger. However, as far as i know, neither of
these
exists in Access. So how would i be able to do this?

Thanks in advance,

- Aaron.

True. SQL Server Unique Constraints allow only 1 Null value. However, he
specified Jet, because he said he knew how to do it in Oracle or SQL Server.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Brendan Reynolds" brenreyn at indigo dot ie wrote in message
...
Actually, in VBA, Null Null is neither True nor False. It is Null. Try
the following in the Immediate window ...

? nullnull
Null

Roger's suggestion will, as far as I can see, work with a Jet database. I
believe it would break if the database were ever moved to SQL Server,
because of the different way in which the two database engines handle Null
values in unique (not primary) indexes.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"TC" wrote in message
...
(NULL != NULL) is False - not True.

HTH,
TC


"Aaron Abdis" wrote in message
...
Ah... okay, i think i see. Then multiple additional locations would
not
break the index because NULL != NULL, correct?

"Roger Carlson" wrote in message
...
Well, here's one way. However, it only checks that one location is
designated as the main location. It does NOT require that at least
one
location MUST be the main.

Anyway, your IS_MAIN field must be a number field (integer or byte
is
best).
This will not work with a Yes/No

1) Set the Default Value of IS_MAIN to null
2) Set the Validation rule to 1
3) Create a Unique (compound) Index on COMPANYID and IS_MAIN

The validation rule ensures that only a 1 or a null can be entered
into
the
field. The unique index ensures that only one IS_MAIN per company
can
have
a 1.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Aaron Abdis" wrote in message
...
Hello,

I have a table called Company_Locations. There is a field named
Is_Main.
For
any given COMPANYID, i want that only one Location can have the
IS_MAIN
checked. How can i do this?

In ORACLE or SQL*Server, i would simply throw a check constraint
on
the
table, or i could use a trigger. However, as far as i know,
neither
of
these
exists in Access. So how would i be able to do this?

Thanks in advance,

- Aaron.

Yes, it seems to work. Thank you Roger.


"Brendan Reynolds" brenreyn at indigo dot ie wrote in message
...
Actually, in VBA, Null Null is neither True nor False. It is Null. Try
the following in the Immediate window ...

? nullnull
Null

Roger's suggestion will, as far as I can see, work with a Jet database. I
believe it would break if the database were ever moved to SQL Server,
because of the different way in which the two database engines handle Null
values in unique (not primary) indexes.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"TC" wrote in message
...
(NULL != NULL) is False - not True.

HTH,
TC


"Aaron Abdis" wrote in message
...
Ah... okay, i think i see. Then multiple additional locations would
not
break the index because NULL != NULL, correct?

"Roger Carlson" wrote in message
...
Well, here's one way. However, it only checks that one location is
designated as the main location. It does NOT require that at least
one
location MUST be the main.

Anyway, your IS_MAIN field must be a number field (integer or byte
is
best).
This will not work with a Yes/No

1) Set the Default Value of IS_MAIN to null
2) Set the Validation rule to 1
3) Create a Unique (compound) Index on COMPANYID and IS_MAIN

The validation rule ensures that only a 1 or a null can be entered
into
the
field. The unique index ensures that only one IS_MAIN per company
can
have
a 1.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Aaron Abdis" wrote in message
...
Hello,

I have a table called Company_Locations. There is a field named
Is_Main.
For
any given COMPANYID, i want that only one Location can have the
IS_MAIN
checked. How can i do this?

In ORACLE or SQL*Server, i would simply throw a check constraint
on
the
table, or i could use a trigger. However, as far as i know,
neither
of
these
exists in Access. So how would i be able to do this?

Thanks in advance,

- Aaron.

No criticism of your solution intended, Roger. Just felt that the point
should be mentioned in the thread, for reference.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"Roger Carlson" wrote in message
...
True. SQL Server Unique Constraints allow only 1 Null value. However, he
specified Jet, because he said he knew how to do it in Oracle or SQL
Server.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Brendan Reynolds" brenreyn at indigo dot ie wrote in message
...
Actually, in VBA, Null Null is neither True nor False. It is Null.
Try
the following in the Immediate window ...

? nullnull
Null

Roger's suggestion will, as far as I can see, work with a Jet database.
I
believe it would break if the database were ever moved to SQL Server,
because of the different way in which the two database engines handle
Null
values in unique (not primary) indexes.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"TC" wrote in message
...
(NULL != NULL) is False - not True.

HTH,
TC


"Aaron Abdis" wrote in message
...
Ah... okay, i think i see. Then multiple additional locations would
not
break the index because NULL != NULL, correct?

"Roger Carlson" wrote in message
...
Well, here's one way. However, it only checks that one location
is
designated as the main location. It does NOT require that at
least
one
location MUST be the main.

Anyway, your IS_MAIN field must be a number field (integer or byte
is
best).
This will not work with a Yes/No

1) Set the Default Value of IS_MAIN to null
2) Set the Validation rule to 1
3) Create a Unique (compound) Index on COMPANYID and IS_MAIN

The validation rule ensures that only a 1 or a null can be entered
into
the
field. The unique index ensures that only one IS_MAIN per company
can
have
a 1.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Aaron Abdis" wrote in message
...
Hello,

I have a table called Company_Locations. There is a field named
Is_Main.
For
any given COMPANYID, i want that only one Location can have the
IS_MAIN
checked. How can i do this?

In ORACLE or SQL*Server, i would simply throw a check constraint
on
the
table, or i could use a trigger. However, as far as i know,
neither
of
these
exists in Access. So how would i be able to do this?

Thanks in advance,

- Aaron.

Gak! Falls on face in mud. :-)

TC


"Brendan Reynolds" brenreyn at indigo dot ie wrote in message
...
Actually, in VBA, Null Null is neither True nor False. It is Null. Try
the following in the Immediate window ...

? nullnull
Null

Roger's suggestion will, as far as I can see, work with a Jet database. I
believe it would break if the database were ever moved to SQL Server,
because of the different way in which the two database engines handle Null
values in unique (not primary) indexes.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"TC" wrote in message
...
(NULL != NULL) is False - not True.

HTH,
TC


"Aaron Abdis" wrote in message
...
Ah... okay, i think i see. Then multiple additional locations would
not
break the index because NULL != NULL, correct?

"Roger Carlson" wrote in message
...
Well, here's one way. However, it only checks that one location is
designated as the main location. It does NOT require that at least
one
location MUST be the main.

Anyway, your IS_MAIN field must be a number field (integer or byte
is
best).
This will not work with a Yes/No

1) Set the Default Value of IS_MAIN to null
2) Set the Validation rule to 1
3) Create a Unique (compound) Index on COMPANYID and IS_MAIN

The validation rule ensures that only a 1 or a null can be entered
into
the
field. The unique index ensures that only one IS_MAIN per company
can
have
a 1.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Aaron Abdis" wrote in message
...
Hello,

I have a table called Company_Locations. There is a field named
Is_Main.
For
any given COMPANYID, i want that only one Location can have the
IS_MAIN
checked. How can i do this?

In ORACLE or SQL*Server, i would simply throw a check constraint
on
the
table, or i could use a trigger. However, as far as i know,
neither
of
these
exists in Access. So how would i be able to do this?

Thanks in advance,

- Aaron.