Use BookShopDB
--Add Primary Key Constraints to 11 Tables
ALTER TABLE Books
ADD CONSTRAINT books_pk PRIMARY KEY (TitleID)

ALTER TABLE BookCondition
ADD CONSTRAINT bookcondition_pk PRIMARY KEY (ConditionID)

ALTER TABLE BookOrders
ADD CONSTRAINT bookorders_pk PRIMARY KEY (OrderID, TitleID)

ALTER TABLE Customers
ADD CONSTRAINT customers_pk PRIMARY KEY (CustomerID)

ALTER TABLE Orders 
ADD CONSTRAINT orders_pk PRIMARY KEY (OrderID)

ALTER TABLE Employees
ADD CONSTRAINT employees_pk PRIMARY KEY (EmployeeID)

ALTER TABLE Positions
ADD CONSTRAINT positions_pk PRIMARY KEY (PositionID)

ALTER TABLE OrderStatus
ADD CONSTRAINT orderstatus_pk PRIMARY KEY (StatusID)

ALTER TABLE FormOfPayment
ADD CONSTRAINT formofpayment_pk PRIMARY KEY (PaymentID)

ALTER TABLE Authors
ADD CONSTRAINT authors_pk PRIMARY KEY (AuthorID)

ALTER TABLE BookAuthors
ADD CONSTRAINT bookauthors_pk PRIMARY KEY (AuthorID, TitleID)

--Add 10 Foreign Key Constraints to 11 Tables
ALTER TABLE BookAuthors
ADD CONSTRAINT authorid_fk FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID)

ALTER TABLE BookAuthors
ADD CONSTRAINT titleid_fk FOREIGN KEY (TitleID)
REFERENCES Books (TitleID)

ALTER TABLE Books
ADD CONSTRAINT conditionid_fk FOREIGN KEY (ConditionID)
REFERENCES BookCondition (ConditionID)

ALTER TABLE BookOrders
ADD CONSTRAINT orderid_fk FOREIGN KEY (OrderID)
REFERENCES Orders (OrderID)

ALTER TABLE BookOrders
ADD CONSTRAINT titleid2_fk FOREIGN KEY (TitleID)
REFERENCES Books (TitleID)

ALTER TABLE Orders
ADD CONSTRAINT customerid_fk FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID)

ALTER TABLE Orders
ADD CONSTRAINT employeeid_fk FOREIGN KEY (EmployeeID)
REFERENCES Employees (EmployeeID)

ALTER TABLE Orders
ADD CONSTRAINT paymentid_fk FOREIGN KEY (PaymentID)
REFERENCES FormOfPayment (PaymentID)

ALTER TABLE Orders
ADD CONSTRAINT statusid_fk FOREIGN KEY (StatusID)
REFERENCES OrderStatus (StatusID)

ALTER TABLE Employees
ADD CONSTRAINT positionid_fk FOREIGN KEY (PositionID)
REFERENCES Positions (PositionID)

--Add 10 CHECK constraints columns from Business Rules on Customers, Authors & Books tables
ALTER TABLE Customers --Must supply either First or Last Name
ADD CONSTRAINT checknames_ck CHECK
(FirstName NOT LIKE 'unknown' OR LastName NOT LIKE 'unknown')

ALTER TABLE Authors --Year born: from 1600 to 2099
ADD CONSTRAINT authors1_ck 
CHECK (YearBorn LIKE ('[1-2][0,6-9][0-9][0-9]')
      OR (YearBorn = 'N/A'))

ALTER TABLE Authors 
ADD CONSTRAINT authors2_ck 
CHECK (YearBorn NOT LIKE '[1][0][0-9][0-9]')

ALTER TABLE Authors 
ADD  CONSTRAINT authors3_ck 
CHECK (YearBorn NOT LIKE '[2][6-9][0-9][0-9]')

ALTER TABLE Authors 
ADD CONSTRAINT authors4_ck 
CHECK (YearDied LIKE ('[1-2][0,6-9][0-9][0-9]')
      OR (YearDied = 'N/A'))

ALTER TABLE Authors 
ADD  CONSTRAINT authors5_ck 
CHECK (YearDied NOT LIKE '[1][0][0-9][0-9]')

ALTER TABLE Authors 
ADD  CONSTRAINT authors6_ck 
CHECK (YearDied NOT LIKE '[2][6-9][0-9][0-9]')

ALTER TABLE Books--Published date, if added, between 1600 and 2099
ADD CONSTRAINT books1_ck 
CHECK (PubDate LIKE ('[1-2][0,6-9][0-9][0-9]')
      OR (PubDate = 'N/A'))

ALTER TABLE Books 
ADD  CONSTRAINT books2_ck 
CHECK (PubDate NOT LIKE '[1][0][0-9][0-9]')

ALTER TABLE Books 
ADD  CONSTRAINT books3_ck 
CHECK (PubDate NOT LIKE '[2][6-9][0-9][0-9]')

sp_helpconstraint Authors