Primary, Foreign Keys in SQL Server - Constraint Tips

To find primary and foreign keys in table, alter, drop, you can use the below scripts
USE TEMPDB
GO
sp_pkeys T_Table --- To find the existing primary keys in table
GO
sp_fkeys T_Table --- To find the existing foreign keys in table
Primary Key
ALTER TABLE T_Table ADD CONSTRAINT PK_T_Table PRIMARY KEY CLUSTERED (ID) -- Add primary key in existing table
GO
Table Level Primary key Creation
CREATE TABLE T_TABLE
(
ID int
CONSTRAINT PK_T_Table PRIMARY KEY CLUSTERED,
COMPANY VARCHAR(10)
)
GO
Drop Primary Key Constraint
ALTER TABLE T_Table DROP CONSTRAINT PK_T_Table
GO

FOREIGN KEY
OPTION 1:
MarketingPersonID int NULL
REFERENCES MarketingPerson(MarketingPersonID)
GO
OPTION 2:
FOREIGN KEY (MarketingPersonID) REFERENCES MarketingPerson(MarketingPersonID)
GO
OPTION 3:
CONSTRAINT FK_DiscountOfferProduct_SalesOrderDetail FOREIGN KEY
(ProductID, DiscountOfferID)
REFERENCES DiscountOfferProduct (ProductID, DiscountOfferID)
UNIQUE CONSTRAINTS:
Table level
Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED
Alter table Creation
ALTER table T_Table
ADD CONSTRAINT UN_T_Table
UNIQUE (Email,productid)
Drop unique constraint
ALTER TABLE T_Table DROP CONSTRAINT UN_T_Table
DEFAULT DEFINITIONS
DEFAULT 'Claim Not Confimed yet'
DEFAULT (getdate())
CHECK CONSTRAINTS
1. CHECK (CreditRating >= 1 and CreditRating <= 5)
2. CONSTRAINT CK_sal_id CHECK (sal_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR sal_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]') CHECK (sal_id IN ('1389', '0736', '0877', '1622', '1756') OR sal_id LIKE '99[0-9][0-9]')