quote:

---

E. Adding an unverified CHECK constraint to an existing column
The following example adds a constraint to an existing column in the table. The column has a value that violates the constraint. Therefore, WITH NOCHECK is used to prevent the constraint from being validated against existing rows, and to allow for the constraint to be added.

Copy Code
CREATE TABLE doc_exd ( column_a INT) ;
GO
INSERT INTO doc_exd VALUES (-1) ;
GO
ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE doc_exd ;
GO



F. Adding a DEFAULT constraint to an existing column
The following example creates a table with two columns and inserts a value into the first column, and the other column remains NULL. A DEFAULT constraint is then added to the second column. To verify that the default is applied, another value is inserted into the first column, and the table is queried.

Copy Code
CREATE TABLE doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO doc_exz (column_a)
VALUES ( 7 ) ;
GO
ALTER TABLE doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO doc_exz (column_a)
VALUES ( 10 ) ;
GO
SELECT * FROM doc_exz ;
GO
DROP TABLE doc_exz ;
GO



G. Adding several columns with constraints
The following example adds several columns with constraints defined with the new column. The first new column has an IDENTITY property. Each row in the table has new incremental values in the identity column.

Copy Code
CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE doc_exe ADD

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,

-- Add a column that references another column in the same table.
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE doc_exe ;
GO

---