Is there a way to set a primary index to ignore Nulls and if so how
would I do it. I have already tried setting it so it is not a required
field, but that does not work. It states that the field can not
contain null values.
7  2912 
A primary key is a field that ensures uniqueness.
The null value is unknown.
If it is unknown, it cannot be said to be unique.
Therefore a Null in a primary key is a contradiction in terms.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Cyberwolf" <jg******@fishm antobin.com> wrote in message
news:11******** *************@e 56g2000cwe.goog legroups.com... Is there a way to set a primary index to ignore Nulls and if so how
would I do it. I have already tried setting it so it is not a required
field, but that does not work. It states that the field can not
contain null values.
"Cyberwolf" <jg******@fishm antobin.com> wrote in message
news:11******** *************@e 56g2000cwe.goog legroups.com... Is there a way to set a primary index to ignore Nulls and if so how
would I do it. I have already tried setting it so it is not a required
field, but that does not work. It states that the field can not
contain null values.
You cannot do this for your primary key.
If you want a field to have a unique index but ignore nulls, then you can
have this but it is not the primary key. Just set the properties:
Required=No
Indexed=Yes (No Duplicates)
"Anthony England" <ae******@oops. co.uk> wrote in message
news:dv******** **@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com... "Cyberwolf" <jg******@fishm antobin.com> wrote in message
news:11******** *************@e 56g2000cwe.goog legroups.com... Is there a way to set a primary index to ignore Nulls and if so how
would I do it. I have already tried setting it so it is not a required
field, but that does not work. It states that the field can not
contain null values.
You cannot do this for your primary key.
If you want a field to have a unique index but ignore nulls, then you can
have this but it is not the primary key. Just set the properties:
Required=No
Indexed=Yes (No Duplicates)
(No Duplicates) could fail if more then one record had Null in the field.
Also any new records after the 1st Null would require a value for the insert
to succeed.
"paii, Ron" <pa**@packairin c.com> wrote in message
news:k_******** ************@at henet.net...
"Anthony England" <ae******@oops. co.uk> wrote in message
news:dv******** **@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com... "Cyberwolf" <jg******@fishm antobin.com> wrote in message
news:11******** *************@e 56g2000cwe.goog legroups.com... > Is there a way to set a primary index to ignore Nulls and if so how
> would I do it. I have already tried setting it so it is not a required
> field, but that does not work. It states that the field can not
> contain null values.
You cannot do this for your primary key.
If you want a field to have a unique index but ignore nulls, then you can
have this but it is not the primary key. Just set the properties:
Required=No
Indexed=Yes (No Duplicates)
(No Duplicates) could fail if more then one record had Null in the field.
Also any new records after the 1st Null would require a value for the
insert
to succeed.
I just created a table with two fields:
F1 = Long Integer, Not Required, Indexed No Duplicates, Not Required
F2 = Text, Required, Do Not Allow Zero Length, Not Indexed
I could enter the following records:
ID F1
1 ABC
Null DEF
Null GHI
2 ABC
However, any attempt to add another record with ID=1 or ID=2 is rejected.
Could you be mistaken in your assertion?
"paii, Ron" <pa**@packairin c.com> wrote in
news:k_******** ************@at henet.net: (No Duplicates) could fail if more then one record had Null in the
field. Also any new records after the 1st Null would require a value
for the insert to succeed.
Null does not equal null.
Why would no duplicates preclude multiple nulls?
--
Lyle Fairfield
"Anthony England" <ae******@oops. co.uk> wrote in message
news:dv******** **@nwrdmz01.dmz .ncs.ea.ibs-infra.bt.com...
"paii, Ron" <pa**@packairin c.com> wrote in message
news:k_******** ************@at henet.net...
"Anthony England" <ae******@oops. co.uk> wrote in message
news:dv******** **@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com... "Cyberwolf" <jg******@fishm antobin.com> wrote in message
news:11******** *************@e 56g2000cwe.goog legroups.com...
> Is there a way to set a primary index to ignore Nulls and if so how
> would I do it. I have already tried setting it so it is not a
required > field, but that does not work. It states that the field can not
> contain null values.
You cannot do this for your primary key.
If you want a field to have a unique index but ignore nulls, then you
can have this but it is not the primary key. Just set the properties:
Required=No
Indexed=Yes (No Duplicates)
(No Duplicates) could fail if more then one record had Null in the
field. Also any new records after the 1st Null would require a value for the
insert
to succeed.
I just created a table with two fields:
F1 = Long Integer, Not Required, Indexed No Duplicates, Not Required
F2 = Text, Required, Do Not Allow Zero Length, Not Indexed
I could enter the following records:
ID F1
1 ABC
Null DEF
Null GHI
2 ABC
However, any attempt to add another record with ID=1 or ID=2 is rejected.
Could you be mistaken in your assertion?
I very well could be mistaken. It just appears wrong to create an index that
allows null.
I have created tables with numeric index fields where if I allowed 0, would
fail on any new records. I assumed null would do the same.
"paii, Ron" <pa**@packairin c.com> wrote in message
news:Ku******** *************** *******@athenet .net...
"Anthony England" <ae******@oops. co.uk> wrote in message
news:dv******** **@nwrdmz01.dmz .ncs.ea.ibs-infra.bt.com...
"paii, Ron" <pa**@packairin c.com> wrote in message
news:k_******** ************@at henet.net... >
> "Anthony England" <ae******@oops. co.uk> wrote in message
> news:dv******** **@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...
>> "Cyberwolf" <jg******@fishm antobin.com> wrote in message
>> news:11******** *************@e 56g2000cwe.goog legroups.com...
>> > Is there a way to set a primary index to ignore Nulls and if so how
>> > would I do it. I have already tried setting it so it is not a required >> > field, but that does not work. It states that the field can not
>> > contain null values.
>>
>> You cannot do this for your primary key.
>> If you want a field to have a unique index but ignore nulls, then you can >> have this but it is not the primary key. Just set the properties:
>> Required=No
>> Indexed=Yes (No Duplicates)
>>
>>
>
> (No Duplicates) could fail if more then one record had Null in the field. > Also any new records after the 1st Null would require a value for the
> insert
> to succeed.
I just created a table with two fields:
F1 = Long Integer, Not Required, Indexed No Duplicates, Not Required
F2 = Text, Required, Do Not Allow Zero Length, Not Indexed
I could enter the following records:
ID F1
1 ABC
Null DEF
Null GHI
2 ABC
However, any attempt to add another record with ID=1 or ID=2 is rejected.
Could you be mistaken in your assertion?
I very well could be mistaken. It just appears wrong to create an index
that
allows null.
I have created tables with numeric index fields where if I allowed 0,
would
fail on any new records. I assumed null would do the same.
I guess Lyle's point that null does not equal null is another way to look at
it.
In that sense, we haven't got two equal values in the field. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Westcoast Sheri |
last post by:
Which will be a faster lookup of an item by, "color" in the following
mySQL tables: "unique key," "primary key," or just plain "key"??
CREATE TABLE myTable (
number int(11) NOT NULL default '0',
description varchar(50) NOT NULL default '',
color varchar(30) NOT NULL default '',
price decimal(3,2) NOT NULL default '0.00',
UNIQUE KEY ...
|
by: skidvd |
last post by:
Hello:
I have just recently converted to using the InnoDB table type so that
I can enforce FOREIGN key constraints. I have been using MyISAM
tables (accessed via JDBC) successfully for some time. However, I
have just come across a problem with the new configuration that
boggles my mind....
First some configuration data:
|
by: Lannsjo |
last post by:
I need to change my primary key column type from smallint to int.
I have tried:
ALTER TABLE livegroup MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT;
But get an error message certainly since my id-column is primary key
and references other tables as well.
How can I come around this problem?
Need help
/Martin
|
by: Mavis Tilden |
last post by:
Hi all,
So I've been reading the newsgroups, and reading a few books trying to learn
SQL and SQL Server 2000. The books tell me I need a Primary Key, and that
every table should have one. I know (I think) that a Primary Key is a
special field that uniquely identifies each record or row within a table.
My question is this: If I have a...
|
by: sfh |
last post by:
Greetings all,
I have a question concerning primary key types.
In the past, I have always created tables with a primary key as an "int" such as:
CREATE TABLE color_id (
color_id int(10) unsigned NOT NULL auto_increment primary key,
color varchar(45) default NULL
) TYPE=INNODB DEFAULT CHARSET=latin1;
| | |
by: sfh |
last post by:
Greetings all,
(I had posted this in MS SQL on accident, my apologies :( )
I have a question concerning primary key types.
In the past, I have always created tables with a primary key as an "int" such as:
CREATE TABLE color_id (
color_id int(10) unsigned NOT NULL auto_increment primary key,
|
by: gilles27 |
last post by:
I am currently undertaking a review of the primary keys in a SQL Server
2000 database with a view to improving performance of queries.
I have heard that, in the case of compound primary keys, it is
important to select the correct order for the columns within the key.
For instance, imagine a table called OrderLine which has primary key...
|
by: Michael Hannon |
last post by:
Greetings. We're running Postgres 7.3 on an Intel linux box (Redhat
Enterprise server, version 3.0). We find ourselves in an awkward
position: we have a database of attributes relating to students that
uses as its primary key the ID number of the student. This is awkward
for the following reasons.
Our university used to use...
|
by: LurfysMa |
last post by:
Most of the reference books recommend autonum primary keys, but the
Access help says that any unique keys will work.
What are the tradeoffs?
I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?
One table has information about the 50 states in the US. The table
looks...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
