As easy as this syntax is, I had to keep looking it up for about a year. I suppose that’s why you are here. Well, here it is: [cc lang=”sql”] ALTER SCHEMA newschema TRANSFER oldschema.Table [/cc] This will transfer the table defined under “oldschema” and transfer it to “newschema”.
Continue reading ...To add a constraint to an existing table use the alter table statement with the add constraint command. There are four different types of constraints: Primary Key Constraints – Enforces unique values for specified column, can be referenced. Foreign Key Constraints – Enforces a reference to a primary key Unique Constraints – Ensures unique values within a column Check Constraints – Limits values acceptable for a column based on an evaluation Add unique constraint [cc lang=”sql”] ALTER TABLE dbo.Presidents ADD CONSTRAINT President_unique UNIQUE (President) [/cc] Add constraint to test value [cc lang=”sql”] ALTER TABLE dbo.Presidents ADD CONSTRAINT President_unique CHECK (YearsInOffice >= 0 AND YearsInOffice < 13) [/cc]
Continue reading ...You can add a default value to a new OR existing column in SQL Server without dropping it. Specifying a default constraint should not cause any blocking. Only when setting a column to NULL OR NOT NULL will cause blocking. But to set the NULLABLE attribute, you need to use ALTER COLUMN instead. When adding this default value, it’s always best to specify the constraint name. If you don’t the system will generate a random constraint name instead. This can wreak havoc because this default constraint name will not be the same name generated on your dev / stage / production systems. This is why it’s always best to specify the name. Add Default Value to Existing Column [cc lang=”sql”] — Add default to existing column DateOfHire: ALTER TABLE [dbo].[Employees] ADD CONSTRAINT DF_Employees_DateOfHire DEFAULT (GETDATE()) FOR [DateOfHire] — Add default value to existing column IsTerminated ALTER TABLE [dbo].[Employees] ADD CONSTRAINT DF_Employees_IsTerminated DEFAULT (0) FOR [IsTerminated] [/cc] Add New Column with Default Value [cc lang=”sql”] — Add new column DateOfHire with default ALTER TABLE Employees ADD DateOfHire datetime CONSTRAINT DF_Employees_DateOfHire DEFAULT (GETDATE()) — Add new column IsTerminated with default (no constraint name specified NOT RECOMMENDED) ALTER TABLE Employees ADD IsTerminated datetime DEFAULT (0) [/cc] Add Default Value with Create Table [cc lang=”sql”] CREATE TABLE [dbo].[Employees] ( [EmployeeID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) NULL, [LastName] [varchar](50) NULL, [SSN] [varchar](9) NULL, — Add default of zero [IsTerminated] [bit] NOT NULL CONSTRAINT DF_Employees_IsTerminated DEFAULT (0) , — Add default of getdate() [DateAdded] [datetime] […]
Continue reading ...To Add a foreign key to a column in an existing table, use ALTER TABLE ADD CONSTRAINT [cc lang=”sql”] ALTER TABLE dbo.President_Lookup ADD CONSTRAINT fk_PresidentID FOREIGN KEY (PresidentID) REFERENCES dbo.Presidents (PresidentID) [/cc]
Continue reading ...To add a constraint to an existing table use the alter table statement with the add constraint command. There are four different types of constraints: Primary Key Constraints – Enforces unique values for specified column, can be referenced. Foreign Key Constraints – Enforces a reference to a primary key Unique Constraints – Ensures unique values within a column Check Constraints – Limits values acceptable for a column based on an evaluation Add unique constraint [cc lang=”sql”] ALTER TABLE dbo.Presidents ADD CONSTRAINT President_unique UNIQUE (President) [/cc] Add constraint to test value [cc lang=”sql”] ALTER TABLE dbo.Presidents ADD CONSTRAINT President_unique CHECK (YearsInOffice >= 0 AND YearsInOffice < 13) [/cc]
Continue reading ...
