Microsoft® SQL Server® 2012 Bible

294

Part II: Building Databases and Working with Data

CONSTRAINT PK_Address_AddressID PRIMARY KEY,

Address1 varchar(75) NOT NULL,

City varchar(75) NOT NULL,

State char(3) NOT NULL,

County varchar(50) NULL,

PostalCode varchar(10)

)

Now that a table is created, execute the following INSERT commands, which reference the

columns in varying order, inserting one row and then multiple rows:

Use AdventureWorks

Go

INSERT INTO dbo.Address(City, State, Address1, PostalCode)

VALUES('Houston', 'TX', '1411 Mesa Road', 77016);

INSERT INTO dbo.Address(City, State, Address1, County, PostalCode)

VALUES('Baton Rouge', 'LA', '444 Perkins Road', 'East Baton Rouge',

70808), ('Chicago', 'IL', '8765 Buttonwood Walk', 'Cook', 60429);

The following SELECT command verifi es the insert:

Use AdventureWorks

go

SELECT AddressID, City, State, Address1, County, PostalCode

FROM dbo.Address;

Result (your result may differ depending on the data loaded into the database):

AddressID City State Address1 County PostalCode

--------- ----------- ------ ------------------- ------------- ---------

1 Houston TX 1411 Mesa Road NULL 77016

2 Baton Rouge LA 444 Perkins Road East Baton Rouge 70808

3 Chicago IL 8765 Buttonwood Walk Cook 60429

Not every column in the table must be listed, but if a column appears, then a value must

be available for the INSERT command. The fi rst INSERT statement in the previous sample

code omitted the County column. The INSERT operation worked nonetheless and inserted

a NULL into the omitted column.

If the County column had a default constraint, then the default value would have been

inserted instead of the NULL. When a column has both no default and a NOT NULL con-

straint, and no value is provided in the INSERT statement, the INSERT operation fails.

You can explicitly force the INSERT of a default without knowing the default value. If the

keyword DEFAULT is provided in the value-column list, then SQL Server stores the default

value for the column. This is a good practice because it documents the intention of the

c12.indd 294c12.indd 294 7/30/2012 4:42:38 PM7/30/2012 4:42:38 PM

http://www.it-ebooks.info