- Create table with Primary Key<\/a><\/li>
- Table with Identity Column<\/a><\/li>
- Create table with Foreign Key<\/a><\/li>
- Table with Default constraint<\/a><\/li>
- Table with Check constraint<\/a><\/li><\/ul><\/div><\/div>\n\n\n\n
Basic Syntax for creating a table is,<\/p>\n\n\n\n
CREATE TABLE databaseName.tableName( \n columnName1 datatype [NULL | NOT NULL], \n columnName2 datatype [NULL | NOT NULL] \n);<\/pre>\n\n\n\n
databaseName – Name of the DB where the table will be created<\/p>\n\n\n\n
tableName \u2013 Name of the table. <\/p>\n\n\n\n
columnName \u2013 Name of the column or field. <\/p>\n\n\n\n
datatype \u2013 Type of the data to be stored in the column. <\/p>\n\n\n\n
The column can be either null or non-null. But it is not mandatory to specify the nullability. It is purely optional to define the nullability of the column. If it is not specified, it is considered as NULL by default. Even though it is not mandatory, it is always a good practice to specify that.<\/p>\n\n\n\n
Now that we have seen the basic syntax of create statement, we will see the different types of it with examples.<\/p>\n\n\n\n
Create table with Primary Key<\/h2>\n\n\n\n
Syntax to create a table with primary key constraint<\/strong><\/p>\n\n\n\n
CREATE TABLE dbo.tblCountry \n( \n\tID INT NOT NULL, \n\tcountryName VARCHAR(50) NOT NULL, \n\tcountryCode CHAR(3) NOT NULL, \n\tCONSTRAINT PK_tblCountry PRIMARY KEY (ID) \n);<\/pre>\n\n\n\n
PK_tblCountry – Name of the primary key of the tblCountry table.<\/p>\n\n\n\n
Table with Identity Column<\/h2>\n\n\n\n
Syntax to create with an identity column<\/strong><\/p>\n\n\n\n
CREATE TABLE dbo.tblCountry \n( \n\tID INT NOT NULL IDENTITY(1,1), \n\tcountryName VARCHAR(50) NOT NULL, \n\tcountryCode CHAR(3) NOT NULL, \n\tCONSTRAINT PK_tblCountry PRIMARY KEY (ID)\n);<\/pre>\n\n\n\n
IDENTITY(1,1) – the First parameter is the seed value and the second parameter is the increment value.<\/p>\n\n\n\n
Create table with Foreign Key<\/h2>\n\n\n\n
Syntax to create with foreign key constraint<\/strong> <\/p>\n\n\n\n
CREATE TABLE dbo.tblPlayer \n( \n\tplayerID INT NOT NULL IDENTITY(1,1), \n\tfirstName VARCHAR(100) NOT NULL, \n\tage INT NOT NULL, \n\tcountryID INT NOT NULL, \n\tcreatedDate DATETIME NOT NULL, \n\tCONSTRAINT PK_tblPlayer PRIMARY KEY (playerID), \n\tCONSTRAINT FK_tblPlayer_tblCountry_countryID FOREIGN KEY([countryID]) REFERENCES [dbo].[tblCountry] ([ID]) \n);<\/pre>\n\n\n\n
FK_tblPlayer_tblCountry_CountryID – Name of the foreign key constraint. <\/p>\n\n\n\n
Table with Default constraint<\/h2>\n\n\n\n
Syntax to create with default constraint<\/strong><\/p>\n\n\n\n
CREATE TABLE dbo.tblPlayer \n( \n\tplayerID INT NOT NULL IDENTITY(1,1), \n\tfirstName VARCHAR(100) NOT NULL, \n\tage INT NOT NULL, \n\tcountryID INT NOT NULL, \n\tcreatedDate DATETIME NOT NULL CONSTRAINT DF_tblPlayer_createdDate DEFAULT GETDATE(), \n\tCONSTRAINT PK_tblPlayer PRIMARY KEY (playerID), \n\tCONSTRAINT FK_tblPlayer_tblCountry_countryID FOREIGN KEY([countryID]) REFERENCES [dbo].[tblCountry] ([ID]) \n);<\/pre>\n\n\n\n
DF_tblPlayer_CreatedDate – Name of the default constraint. <\/p>\n\n\n\n
Table with Check constraint<\/h2>\n\n\n\n
Syntax to create with a check constraint<\/strong><\/p>\n\n\n\n
CREATE TABLE dbo.tblPlayer \n( \n\tplayerID INT NOT NULL IDENTITY(1,1), \n\tfirstName VARCHAR(100) NOT NULL, \n\tage INT NOT NULL CONSTRAINT CK_tblPlayer_age CHECK (age >= 15), \n\tcountryID INT NOT NULL, \n\tcreatedDate DATETIME NOT NULL CONSTRAINT DF_tblPlayer_createdDate DEFAULT GETDATE(), \n\tCONSTRAINT PK_tblPlayer PRIMARY KEY (playerID), \n\tCONSTRAINT FK_tblPlayer_tblCountry_countryID FOREIGN KEY([countryID]) REFERENCES [dbo].[tblCountry] ([ID]) \n);<\/pre>\n\n\n\n
CK_tblPlayer_Age – Name of the check constraint. <\/p>\n\n\n\n
In the above examples, the constraints were created with meaningful names. Please refer to the below screenshot.<\/p>\n\n\n\n
![\"Create](\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/07\/image-2-1024x123.png\")
<\/a><\/figure>\n\n\n\nSyntax to create with SQL server-generated constraint names. It same as the above technique but the difference is that the name of the constraint will be chosen by SQL Server itself.<\/p>\n\n\n\n
CREATE TABLE dbo.tblPlayer \n( \n\tplayerID INT NOT NULL IDENTITY(1,1), \n\tfirstName VARCHAR(100) NOT NULL, \n\tage INT NOT NULL CHECK (age >= 15), \n\tcountryID INT NOT NULL, \n\tcreatedDate DATETIME NOT NULL DEFAULT GETDATE(), \n\tPRIMARY KEY (playerID), \n\tFOREIGN KEY([countryID]) REFERENCES [dbo].[tblCountry] ([ID]) \n);<\/pre>\n\n\n\n
Check the constraint names generated by SQL server in the below table<\/p>\n\n\n\n
![\"Image](\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/07\/image-3-1024x121.png\")
<\/a><\/figure>\n\n\n\n\n\nAlso, check “CREATE TABLE using SELECT in MS SQL Server<\/a>“<\/p>\n\n\n\n
Reference – Microsoft SQL Server 2017<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
In Microsoft SQL Server, there are different ways in which we can create the table. The table can be created with or without constraints. Basic Syntax for creating a table is, databaseName – Name of the DB where the table will be created tableName \u2013 Name of the table. columnName \u2013 Name of the column\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[10],"tags":[47,42,39,40],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/144"}],"collection":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/comments?post=144"}],"version-history":[{"count":31,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/144\/revisions"}],"predecessor-version":[{"id":597,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/144\/revisions\/597"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=144"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=144"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=144"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- Table with Identity Column<\/a><\/li>