Struggling for Competence

Create Table and Alter Table

Create Table and Alter Table are the mainstays of building a database, but I can never quite remember the syntax.  SQL Server, Create Table [1][2], Alter Table [3].


CREATE TABLE Newspapers
(
    NewspaperID     INT NOT NULL,		
    NewspaperName   VARCHAR(40) NOT NULL,
    Format          VARCHAR(10) NOT NULL
)

-- Adding a primary key adds a *clustered* index too.
ALTER TABLE Newspapers
ADD CONSTRAINT PK_Newspapers PRIMARY KEY (NewspaperID)

ALTER TABLE Newspapers
ADD CONSTRAINT CK_Newspapers_Format CHECK (Format IN ('Tabloid', 'Broadsheet'))

ALTER TABLE Newspapers
ADD CONSTRAINT DF_Newspapers_Format DEFAULT ('Tabloid') FOR Format

CREATE TABLE Articals
(
    ArticalID   INT CONSTRAINT PK_Articals PRIMARY KEY IDENTITY(1,1),
    NewspaperID INT NOT NULL,
    Title       VARCHAR(40) NOT NULL,
    WordCount   INT NOT NULL CONSTRAINT DF_Articals_WordCount DEFAULT (0),
    TS          ROWVERSION
)

ALTER TABLE Articals
ADD CONSTRAINT FK_Articals_Newspapers FOREIGN KEY (NewspaperID)
    REFERENCES Newspapers(NewspaperID)
    
-- Adding a foregin key does not automatically create an index
-- An index is nonclustered by default
CREATE INDEX IX_Articals_NewspaperID ON Articals(NewspaperID)

-- Adding a Unique constraint adds a unique non-clustered index automatically
ALTER TABLE Articals
ADD CONSTRAINT UC_Articles_Title UNIQUE (Title)

DROP TABLE Articals	
DROP TABLE Newspapers