problem with primary key from column group

Question

Post reply

problem with primary key from column group

david myers-292056

SSC-Addicted

Points: 413
More actions
January 27, 2006 at 9:39 am

#167919

Hi,
Can someone please show me the error of my ways. I'm working with MSDE2000 and Web Data Administrator, using the Query builder I created the following table:
CREATE TABLE TABLEA (X INTEGER NOT NULL,
                                Y DATETIME NOT NULL,
                                Z VARCHAR(16)
                  PRIMARY KEY(X, Y));
Then
INSERT INTO TABLEA (X, Y)
                  VALUES(0, 01/02/2003);
To this point everything's OK.
If I
INSERT INTO TABLEA(X, Y)
                  VALUES(0, 01/03/2003);
Produces a Primary key violation error. Why ?
If I change the 1st value everything's OK the
record is inserted.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello David,

When you define a primary key on a table, the entity integrity is enforced on the table.

In Books Online:

PRIMARY KEY Constraints

A table usually has a column or combination of columns whose values uniquely identify each row in the table. This column (or columns) is called the primary key of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or alter a table.

A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints ensure unique data, they are often defined for identity column.

When you specify a PRIMARY KEY constraint for a table, Microsoft® SQL Server™ 2000 enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries.

If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.

Hope you are clear now.

Thanks and have a nice day!!!

Lucky

david myers-292056 SSC-Addicted Points: 413 More actions · Answer 2

You say

"If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique"

I changed only the date column (Y) the combination of values is unique. That's what I don't understand.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

It has nothing to do with the primary key.

It is a syntax error on the INSERT statements. Try this:

INSERT INTO TABLEA (X, Y)

VALUES(0, 01/02/2003);

SELECT * FROM TABLEA

Notice anything ? What date is in the table ?

That's because you need to quote dates with single quotes. The correct syntax is:

INSERT INTO TABLEA (X, Y)

VALUES(0, '01/02/2003' );

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

To conclude then...

Even though different dates were being used, since the syntax was incorrect, SQL Server was storing (or trying to) store the dates as..1900-01-01 00:00:00.000

- hence the "primary key violation" error msg...

From BOL:

"Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date."

**ASCII stupid question, get a stupid ANSI !!!**

david myers-292056 SSC-Addicted Points: 413 More actions · Answer 5

david myers-292056

SSC-Addicted

Points: 413

January 27, 2006 at 1:47 pm

#617566

Thanks very much.

Everybodies got to be a newbi once