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.
CREATE TABLE table_name
(
{col_name column_properties[constraint [constraint [...constraint]]]
| [[, ] constraint]}
[[, ] {next_col_name | next_constraint}
)
[ON segment_name]
where
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.
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.
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 .
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 )
You must have REFERENCES permission to create a FOREIGN KEY constraint to a table you do not own.