Naming FKs in the script

[hwkranger]

how do i name a relationship in the syntaxt

CREATE TABLE ReportPeriod_ReportDate (
  ReportPeriodID INTEGER   NOT NULL,
  ReportDateID INTEGER   NOT NULL,
  PRIMARY KEY(ReportPeriodID, ReportDateID),
  FOREIGN KEY(ReportPeriodID)
    REFERENCES ReportPeriod(ReportPeriodID)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(ReportDateID)
    REFERENCES ReportDate(ReportDateID)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
);

I know how to name it like:

ALTER TABLE [dbo].[Department] ADD 
	CONSTRAINT [FK_DepartmentHead] FOREIGN KEY 
	(
		[DepartmentHeadUniqueID]
	) REFERENCES [dbo].[Employee] (
		[EmployeeUniqueID]
	)

I don't like the default names MSSQL assigns when i use the first syntaxt.

CHEERS!

Randall Vollen
Merrill Lynch

 

[hwkranger]

NM I remember how to do it

  [b]CONSTRAINT FK_Employee_DepartmentID[/b
  FOREIGN KEY(DepartmentID)
    REFERENCES Department(DepartmentID) 
      ON DELETE NO ACTION
      ON UPDATE NO ACTION

Randall Vollen
Merrill Lynch

 

[SQLDenis]

like this

[COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color] ReportPeriod_ReportDate (
  ReportPeriodID [COLOR=blue]INTEGER[/color]   NOT NULL,
  ReportDateID [COLOR=blue]INTEGER[/color]   NOT NULL,
  [COLOR=blue]PRIMARY[/color] [COLOR=blue]KEY[/color](ReportPeriodID, ReportDateID),
  [COLOR=blue]CONSTRAINT[/color] FK_ReportPeriodID  [COLOR=blue]FOREIGN[/color] [COLOR=blue]KEY[/color](ReportPeriodID)
    [COLOR=blue]REFERENCES[/color] ReportPeriod(ReportPeriodID)
      [COLOR=blue]ON[/color] [COLOR=blue]DELETE[/color] [COLOR=blue]NO[/color] [COLOR=blue]ACTION[/color]
      [COLOR=blue]ON[/color] [COLOR=blue]UPDATE[/color] [COLOR=blue]NO[/color] [COLOR=blue]ACTION[/color],
  [COLOR=blue]CONSTRAINT[/color] FK_ReportDateID [COLOR=blue]FOREIGN[/color] [COLOR=blue]KEY[/color](ReportDateID)
    [COLOR=blue]REFERENCES[/color] ReportDate(ReportDateID)
      [COLOR=blue]ON[/color] [COLOR=blue]DELETE[/color] [COLOR=blue]NO[/color] [COLOR=blue]ACTION[/color]
      [COLOR=blue]ON[/color] [COLOR=blue]UPDATE[/color] [COLOR=blue]NO[/color] [COLOR=blue]ACTION[/color])

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[hwkranger]

YAR! I forgot but then I remembered a few minutes later. I couldn't find it on BOL -- but luckily I remembered!

Randall Vollen
Merrill Lynch