Can a SQL Server 2000 table have no PK, and therefore contain duplicate records?

I have an audit table and instead of defining an identity or ticketed column, I'm considering just pushing in the records of the recorded table (via triggers).

Can a SQL Server 2000 table have no PK, and therefore contain duplicate records?

If yes, does all I开发者_Python百科 have to do consist of CREATING the TABLE without defining any constraint on it?

Yes, this is possible, but not necessarily a good idea. Replication and efficient indexing will be quite difficult without a primary key.

Yes a table without a primary key or Unique Constraint can have rows that are duplicated

for example

CREATE TABLE bla(ID INT)


INSERT bla (ID) VALUES(1)
INSERT bla (ID) VALUES(1)
INSERT bla (ID) VALUES(1)


SELECT * FROM bla
GO

Yes a SQL Server 2000 table can have no primary key and contain duplicate records and yes you can simply Create a table without defining any constraint on it. However I would not suggest this.

Instead, since you are creating an audit table for another table. Lets say for this example you have a Person Table and a Person Audit table that tracks changes in the person Table.

Create your Audit Table like this

CREATE TABLE dbo.PersonAuditID
(
  PersonAuditID int NOT NULL IDENTITY (1, 1),
  PersonId int NOT NULL,
  FirstName nvarchar(50) NOT NULL,
  LastName nvarchar(50) NOT NULL,
  PersonWhoMadeTheChange nvarchar(100) NOT NULL,
  TimeOfChange datetime NOT NULL,
  ChangeAction int NOT NULL,
  /* any other fields here*/
  CONSTRAINT [PK_PersonAudit] PRIMARY KEY NONCLUSTERED 
  (
[PersonAuditID] ASC
  )
)  ON [PRIMARY]

This will give you a primary key, and keep records unique to the table. It also provides the ability to track who made the change, when the change was made, and if the change was an insert, update or delete.

Your triggers would look like the following

CREATE TRIGGER Insert_PERSON
   ON  PERSON
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    INSERT INTO PERSONAUDIT
    (PersonID, 
     FirstName, 
     LastName, 
     PersonWhoMadeTheChange, 
     TimeOfChange, 
     ChangeAction,
     ... other fields here
     SELECT 
       PersonID,
       FirstName,
       LastName,
       User(),
       getDate(),
       1,
       ... other fields here
     FROM INSERTED

END

CREATE TRIGGER Update_PERSON
   ON  PERSON
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    INSERT INTO PERSONAUDIT
    (PersonID, 
     FirstName, 
     LastName, 
     PersonWhoMadeTheChange, 
     TimeOfChange, 
     ChangeAction,
     ... other fields here
     SELECT 
       PersonID,
       FirstName,
       LastName,
       User(),
       getDate(),
       2,
       ... other fields here
     FROM INSERTED

END

CREATE TRIGGER Delete_PERSON
   ON  PERSON
   AFTER DELETE
AS 
BEGIN
    SET NOCOUNT ON;
    INSERT INTO PERSONAUDIT
    (PersonID, 
     FirstName, 
     LastName, 
     PersonWhoMadeTheChange, 
     TimeOfChange, 
     ChangeAction,
     ... other fields here
     SELECT 
       PersonID,
       FirstName,
       LastName,
       User(),
       getDate(),
       3,
       ... other fields here
     FROM DELETED

END

SQL Server 2000+, can have tables without PK. And yes, you create them by no using a constraint.

For an audit table, you need to think of what you may be using the audit data for. And even if you are not doing auditing to spefically use to restore records when unfortunate changes were made, they are inevitably used for this. Will it be easier to identify the record you want to restore if you have a surrogate key that prevents you from accidentally restoring 30 other entries when you only want the most recent? Will a key value help you identify the 32,578 records that were deleted in one batch that needs to be restored?

What we do for auditing is have two tables for each table, one stores information about the batch of records changed, including an auto-incrementing id, the user, the application, the datetime, the number of affected records. The child table then used the ID as the fk and stored the details about the old and new values for each record inserted/updated/deleted. This really helps us when a process bug causes many records to be changed by accident.