Saturday, July 14, 2012

make a foreign key in MSSQL

This helped me some with this:

BEGIN TRANSACTION
GO
CREATE TABLE dbo.RegisteredDriver
   (
   RegisteredDriverId uniqueidentifier NOT NULL,
   LicenseNumber int NOT NULL,
   LicenseExpiration datetime NOT NULL,
   IsRequiredToWearGlasses bit NOT NULL
   ) ON [PRIMARY]
GO
ALTER TABLE dbo.RegisteredDriver ADD CONSTRAINT
   PK_RegisteredDriver PRIMARY KEY CLUSTERED
   (
   RegisteredDriverId
   ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
   ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE dbo.Vehicle
   (
   VehicleId uniqueidentifier NOT NULL,
   RegisteredDriverId uniqueidentifier NOT NULL,
   YearMakeModel varchar(50) NOT NULL,
   LicensePlate nchar(7) NOT NULL,
   RegistrationExpiration datetime NOT NULL,
   IsInsured bit NOT NULL
   ) ON [PRIMARY]
GO
ALTER TABLE dbo.Vehicle ADD CONSTRAINT
   PK_Vehicle PRIMARY KEY CLUSTERED
   (
   VehicleId
   ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
   ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Vehicle ADD CONSTRAINT
   FK_Vehicle_Vehicle1 FOREIGN KEY
   (
   VehicleId
   ) REFERENCES dbo.Vehicle
   (
   VehicleId
   ) ON UPDATE NO ACTION
    ON DELETE NO ACTION
GO
ALTER TABLE dbo.Vehicle ADD CONSTRAINT
   FK_Vehicle_RegisteredDriver FOREIGN KEY
   (
   RegisteredDriverId
   ) REFERENCES dbo.RegisteredDriver
   (
   RegisteredDriverId
   ) ON UPDATE NO ACTION
    ON DELETE NO ACTION
GO
COMMIT

No comments:

Post a Comment