INSERTING NULL VALUES

Question

Hi, 

I created a table, and trying to insert NULL in a column, that I presume should allow it, but its not accepting it.

create table INSTRUCT (

INSSN CHAR (9) NOT NULL,
FIRSTNAME CHAR (20),
IMIDINIT CHAR (1) NOT NULL,
PRIMARY KEY (INSSN,FIRSTNAME)
)

i tried this 

INSERT INTO INSTRUCT (INSSN,FIRSTNAME,IMIDINIT)
VALUES ('382949898',NULL,'I');

but its saying column does not allow null

Answer 1

Compound PRIMARY KEY column FIRSTNAME cannot be null.

BOL: "A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column."

LINK: http://technet.microsoft.com/en-us/library/ms191236(v=sql.105).aspx

---

---

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014

Answer 2

The INSSN and firstname columns cannot be left blank or it will not allow NULL entries and it should be unique.

--Prashanth

Answer 3

Compound PRIMARY KEY column FIRSTNAME cannot be null.

BOL: "A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column."

LINK: http://technet.microsoft.com/en-us/library/ms191236(v=sql.105).aspx

---

---

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014

thanks for spotting that.

Answer 4

Compound PRIMARY KEY column FIRSTNAME cannot be null.

BOL: "A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column."

LINK: http://technet.microsoft.com/en-us/library/ms191236(v=sql.105).aspx

---

---

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014

I am trying to explain NULL for a colleague,  and this is the basic i wrote, do u think its suffice for basic

NULL literally means unknown, and its represented by the word NULL without quotes

NULL      !=     ‘NULL’

When creating a table and a column is designated not NULL, it means when inserting Values for the roles on the table, a value that is NOT NULL must be provided for that particular column.

try this basic codes

create table INSTRUCT (

 

INSSN CHAR (9) NOT NULL,

FIRSTNAME CHAR (20),

IMIDINIT CHAR (1) NOT NULL,

PRIMARY KEY (INSSN)

)

 

-------Try this codes if it helps at all

---------this will be permisible

INSERT INTO INSTRUCT (INSSN,FIRSTNAME,IMIDINIT)

VALUES ('334838999','NULL','A');

 

 

---------this will be permisible

INSERT INTO INSTRUCT (INSSN,FIRSTNAME,IMIDINIT)

VALUES ('NULL','NULL','A');

 

---------- this will not be permisible

INSERT INTO INSTRUCT (INSSN,FIRSTNAME,IMIDINIT)

VALUES (NULL,'ALYCE','P');

 

 ---------this will be permisible

INSERT INTO INSTRUCT (INSSN,FIRSTNAME,IMIDINIT)

VALUES ('382949898',NULL,'I');

 

Answer 5

Primary key = NOT NULL + UNIQUE constraint , You no need to explicitly mention NOT NULL constraint while defining the table for INSSN column.

You are right NULL !='NULL', NULL is something is missing and garbage. It doesn't signifies anything. It violates the integrity of the data.

When you define the table with NOT NULL, you can't left the column blank. You've make a right entry into the field.

The examples are ok. You can also look at different ways to manipulate NULL values

http://www.w3schools.com/sql/sql_isnull.asp

--Prashanth

Answer 6

To add to above NULL is not regarded as a value in sql server under default conditions. It represents the condition lack of value or unknown value.

---

Please Mark This As Answer if it solved your issue
Please Mark This As Helpful if it helps to solve your issue
Visakh
----------------------------
My MSDN Page
My Personal Blog
My Facebook Page