Primary Key and Unique

  • Thread starter Thread starter EMW
  • Start date Start date
E

EMW

When I create a table in the database with:

cmd.CommandText = "CREATE TABLE pmdb.wolist (SITENUM NVARCHAR2(5) NOT NULL,
VELD1 NVARCHAR2(100), " + _
"VELD2 NVARCHAR2(100), VELD3 NVARCHAR2(100), VELD4 NVARCHAR2(100), " + _
"VELD5 NVARCHAR2(100), VELD6 NVARCHAR2(100), VELD7 NVARCHAR2(100), " + _
"VELD8 NVARCHAR2(100), PRIMARY KEY(sitenum)) TABLESPACE PM"

This is part of a report and VELD1 in combination of SITENUM is a unique
combination.
Since VELD1 is the date the report is created on and SITENUM is just a
number, I planned to add more reports with the same number but a different
date to the database.

Here comes my problem.
I cannot add more rows to the database, I get an error about violating the
UNIQUE contraint.
But I never specified the UNIQUE contraint, so it should accept multiple
times of SITENUM, but just with another date in VELD1.

If I leave the PRIMARY KEY part out, the UPDATE command of the
OracleDataAdaptor errors with 'No primary key etc..'

So what should my commandtext then be like, in order to have several rows
with the same number in SITENUM and different dates in VELD1?

rg,
Eric
 
EMW said:
When I create a table in the database with:

cmd.CommandText = "CREATE TABLE pmdb.wolist (SITENUM NVARCHAR2(5) NOT NULL,
VELD1 NVARCHAR2(100), " + _
"VELD2 NVARCHAR2(100), VELD3 NVARCHAR2(100), VELD4 NVARCHAR2(100), " + _
"VELD5 NVARCHAR2(100), VELD6 NVARCHAR2(100), VELD7 NVARCHAR2(100), " + _
"VELD8 NVARCHAR2(100), PRIMARY KEY(sitenum)) TABLESPACE PM"

This is part of a report and VELD1 in combination of SITENUM is a unique
combination.
Since VELD1 is the date the report is created on and SITENUM is just a
number, I planned to add more reports with the same number but a different
date to the database.

Here comes my problem.
I cannot add more rows to the database, I get an error about violating the
UNIQUE contraint.
But I never specified the UNIQUE contraint, so it should accept multiple
times of SITENUM, but just with another date in VELD1.
Click to expand...

You've specified that SITENUM is the whole primary key, so you can't
have multiple records with the same SITENUM. You haven't specified
anything different about VELD1 from VELD2 etc.
If I leave the PRIMARY KEY part out, the UPDATE command of the
OracleDataAdaptor errors with 'No primary key etc..'

So what should my commandtext then be like, in order to have several rows
with the same number in SITENUM and different dates in VELD1?
Click to expand...

You shouldn't specify it like that - you need to specify that the
primary key is *both* VELD1 and SITENUM.
 
Thanks, but when I specify two primary keys, I get an error that is not
allowed.
Only one primary key possible.
 
EMW said:
Thanks, but when I specify two primary keys, I get an error that is not
allowed.
Only one primary key possible.
Click to expand...

No, you need to specify *one* primary key constraint with two columns.

To quote some text and an example from the T-SQL manual:

<quote>
Constraints can be column constraints or table constraints:

A column constraint is specified as part of a column definition and
applies only to that column (the constraints in the earlier samples are
column constraints).


A table constraint is declared independently from a column definition
and can apply to more than one column in a table.
Table constraints must be used when more than one column must be
included in a constraint.

For example, if a table has two or more columns in the primary key, you
must use a table constraint to include both columns in the primary key.
Consider a table that records events happening in a computer in a
factory. Assume that events of several types can happen at the same
time, but that no two events happening at the same time can be of the
same type. This can be enforced in the table by including both the type
and time columns in a two-column primary key.

CREATE TABLE factory_process
(event_type int,
event_time datetime,
event_site char(50),
event_desc char(1024),
CONSTRAINT event_key PRIMARY KEY (event_type, event_time) )
</quote>
 
Thanks!!!!

Strange it is... placing the two fields in the PRIMARY KEY was exactly wat I
did at first and it wasn't excepted...
Now I tried it again and it worked....

Thanks for the help!
Eric.
 
Back
Top