How to: Create Database Objects Using Table Designer
Not only is the new SQL Server node in SQL Server Object Explorer very similar to SSMS visually, but you can create new objects using contextual menus that function like their SSMS-counterparts.
For example, you can create a new database under the Databases node. Similarly, you can select a specific database and create or edit table definitions and their related programming objects on-the-fly using the new Table Designer. From the Table Designer, you can switch to a script pane which allows you to directly edit the script that defines this table.
To create a new database
In SQL Server Object Explorer, under the SQL Server node, expand your connected server instance.
Right-click the Databases node and select Add New Database.
Rename the new database to Trade.
To create new tables using the Table Designer
Expand the newly created Trade node. Right-click the Tables node and select Add New Table.
The Table Designer opens in a new window. The designer consists of the Columns Grid, Script Pane and Context Pane. The Columns Grid lists all the columns in the table. We will revisit other components of the designer in later procedures.
In the Script Pane, rename the new table to Suppliers. Specifically, replace
CREATE TABLE [dbo].[Table1]with
CREATE TABLE [dbo].[Suppliers]Click the empty row in the Columns Grid to add a new column to the table. Enter CompanyName for the Name field, nvarchar (128) for Data Type and uncheck the Allow Nulls field. As you tab away from the fields, notice that the Script Pane is updated immediately.
Add another new column. Enter Address for the Name field, nvarchar (MAX) for Data Type and uncheck the Allow Nulls field.
Warning
When you are editing objects from a connected database, do not save them to your local drive. To save your changes to the database properly, follow the steps in the next How to: Update a Connected Database with Power Buffer procedure.
Repeat the above steps to create another table named Customer. This time, add the following columns to the Customer table using the Columns Grid. And remember to change the script so that the table’s name is [dbo].[Customer].
Name
Data Type
Allow Nulls
Id
int
unchecked
Name
nvarchar (128)
unchecked
Create one more table named Products. Add the following columns to the Products table using the Columns Grid. And remember to change the script so that the table’s name is [dbo].[Products].
Name
Data Type
Allow Nulls
Id
int
unchecked
Name
nvarchar (128)
unchecked
ShelfLife
int
checked
SupplierId
int
checked
CustomerId
int
checked
To create a new check constraint using the Table Designer
The Context Pane of the Table Designer gives you a logical view of the table definition (Keys, Constraints, Triggers, etc.), and enables you to select an object to highlight its relationships to individual columns.
For the Products table, right-click the Check Constraints node in the Context Pane of the table designer, and select Add New Check Constraint.
Notice that the node count automatically increments by 1.
Click the Script Pane, and replace the default definition of the constraint with the following.
CONSTRAINT [CK_Products_ShelfLife] CHECK ([ShelfLife] <5),This constraint will limit the value of ShelfLife for a row to be under 5.
To create new foreign key references using the Table Designer
For the Products table, right-click the Foreign Keys node in the Context Pane, and select Add New Foreign Key.
Notice that the node count automatically increments by 1.
Click the Script Pane, and replace the default definition of the foreign key reference with the following.
CONSTRAINT [FK_Products_SupplierId] FOREIGN KEY ([SupplierId]) REFERENCES [dbo].[Suppliers] ([Id]),Repeat the steps above to add another foreign key reference to the Products table. This time, replace the default definition with the following.
CONSTRAINT [FK_Products_CustomerId] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([Id])