none
Composite PRIMARY KEY

    Allgemeine Diskussion

  • I am not a fan a composite PRIMARY KEY with the exception of junction tables.

    If a set of columns happens to be the NATURAL KEY, you can just make them the UNIQUE KEY clustered or nonclustered.

    I just don't see how on Earth can someone justify composite FOREIGN KEY and spreading content around to FK tables?

    I don't see any benefit of composite PRIMARY KEY over INT IDENTITY SURROGATE PK and composite UNIQUE KEY.

    Example of my design philosophy:

    CREATE TABLE HollywoodStar (
    	HollywoodStarID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    	LastName nvarchar(40) not null,
    	FirstName nvarchar(32) default (SPACE(0)),
    	MiddleName nvarchar(32) default (SPACE(0)),
    	CONSTRAINT [UQLnameFnameMname] UNIQUE CLUSTERED 
    	(
    		LastName ASC,
    		FirstName ASC,
    		MiddleName ASC
    	),
    	Birthdate date not null,
    	BirthName nvarchar(128),
    	Profession nvarchar(128) not null,
    	ModifiedDate date default (CURRENT_TIMESTAMP));


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Mittwoch, 24. Oktober 2012 11:40
    Moderator

Alle Antworten