Adding Constraints In The CREATE TABLE Statement

Steve Jones shows how you can add constraints in your CREATE TABLE statement:

A good habit to get into is to explicitly name your constraints. I try to do this when I create tables to be sure that a) I have a PK and b) it’s named the same for all environments.

I can create a PK inline, with a simple table like this:

CREATE TABLE Batting
   (
        BattingKey INT NOT NULL CONSTRAINT BattingPK PRIMARY KEY
        , PlayerID INT
        , BattingDate DATETIME
        , AB TINYINT
        , H TINYINT
        , HR tinyint
   )
;

This gives a primary key, named “BattingPK, that I can easily see inline with the column.

Steve also gives an alternative formulation which works well for composite keys.  You can additionally add constraints after the create statement, but if you are creating temp tables and want to take advantage of temp table reuse, constraints have to be created as part of the table (and cannot have names).  For additional fun, since SQL Server 2014, you can create indexes as part of the CREATE TABLE statement as well—that was needed to create memory-optimized tables as back in that edition, you couldn’t add new indexes after the fact.

Related Posts

Reading SQL Server Error Logs

Thomas Rushton has a script for us: Why Script This? What’s Wrong With SSMS’s GUI?Well, although SSMS does allow you to look at the error logs, it’s not very helpful for filtering – you can only filter for items that match, rather than exclude items. There are a few other filters as well – I […]

Read More

Rounding to Intervals in Various Languages

Dave Mason doesn’t like rounding to intervals very much in T-SQL: If I had to choose any of these options for production, I’d probably go with Query 1, just because I think it would make the most sense to any other developer that might encounter it (including future me, who probably wouldn’t remember writing the […]

Read More

Categories

November 2018
MTWTFSS
« Oct Dec »
 1234
567891011
12131415161718
19202122232425
2627282930