CREATE TABLE Statement (version 6.5)

Defers constraints during replication by using the NOT FOR REPLICATION clause. The current user must have REFERENCES permission to the table(s) to which any foreign key constraints refer.

For additional syntax information for the CREATE TABLE statement, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

CREATE TABLE table_name
(
    {col_name column_properties[constraint [constraint [...constraint]]]
    | [[, ] constraint]}
        [[, ] {next_col_name | next_constraint}
)
[ON segment_name]

where

table_name

Is the name of the new table.

col_name

Is the name of a column in the table.

column_properties

Specifies the datatype, null values, or identity values for the column.

Note Using the IDENTITY column property implies a value of NOT NULL. Because IDENTITY requires that a column have a value, NOT NULL is superfluous.

constraint

Specifies a table-or column-level constraint. The NOT FOR REPLICATION clause suspends column-level, foreign key, and check constraints during replication.

The REFERENCES permission must be placed on tables where non-table owners must create foreign keys.

Note Table-level default constraints are not supported within the CREATE TABLE statement.

Note The datatypes of the primary key and the foreign must match exactly.

ON segment_name

If specified with a PRIMARY KEY or UNIQUE constraint, creates only the index on the named segment.

Remarks

REFERENCES permissions are not checked during data manipulation language (DML) operations.

For example, suppose table country_table already exists and you are creating table city_table with a FOREIGN KEY constraint to the country_table table. You must have REFERENCES permission to country_table in order to create the FOREIGN KEY constraint.

Users who need access only to city_table must only have SELECT permission to city_table; they do not need access to country_table.

For information about disabling the REFERENCES permission, see Trace Flags .

Example

This example defines a table with a foreign key constraint called p1_constraint that can be deferred during replication.

CREATE TABLE employee
(
    emp_id    integer
        CONSTRAINT p1_constraint PRIMARY KEY NONCLUSTERED,
    fname         CHAR(20) NOT NULL,
    minitial        CHAR(1) NULL,
    lname        VARCHAR(30) NOT NULL,
    job_id         SMALLINT NOT NULL
        DEFAULT 1
        REFERENCES jobs(job_id) NOT FOR REPLICATION
)
  

Permission

You must have REFERENCES permission to create a FOREIGN KEY constraint to a table you do not own.