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 , Alter Table .
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