Primary Key Creation

[Terry]

I have two questions:

1) I am creating a table with a SQL statement that takes data from another
ODBC linked table. The PK of the source table is created as a non-PK number
in the created table everytime the SQL is executed. How can I ensure that
the number is created as a PK?

2) I have a link to an Excel worksheet range. The range has a column of
sequencial numbers that I would like to use as a PK in table relationships.
Is that possible, or do I have to do something different?

Regards

 

[John Vinson]

I have two questions:

1) I am creating a table with a SQL statement that takes data from another
ODBC linked table. The PK of the source table is created as a non-PK number
in the created table everytime the SQL is executed. How can I ensure that
the number is created as a PK?

2) I have a link to an Excel worksheet range. The range has a column of
sequencial numbers that I would like to use as a PK in table relationships.
Is that possible, or do I have to do something different?

Regards

In both cases, you might want to consider creating a permanent table
(with the desired indexes, primary key, formats, datatypes etc.) in
your database; rather than repeatedly running MakeTable queries, you
could link to the ODBC table or the spreadsheet and Append the data.
When you're ready for another import, simply run a query

DELETE * FROM localtable;

to empty it in preparation for the new data.

Whether you use make-tables or appends, be sure to Compact the
database frequently, as the space used for the table (or the data) is
not recovered when it's deleted.

John W. Vinson[MVP]
(no longer chatting for now)

 

[Jamie Collins]

1) I am creating a table with a SQL statement that takes data from another
ODBC linked table. The PK of the source table is created as a non-PK number
in the created table everytime the SQL is executed. How can I ensure that
the number is created as a PK?

What sql are you using? e.g.

CREATE TABLE MyNewTable
(
ID INTEGER NOT NULL,
CONSTRAINT pk__mytable PRIMARY KEY (ID)
)
;

2) I have a link to an Excel worksheet range. The range has a column of
sequencial numbers that I would like to use as a PK in table relationships.
Is that possible, or do I have to do something different?

I don't think so:

ALTER TABLE MyNewTable
ADD CONSTRAINT fk__mynew2
FOREIGN KEY (ID)
REFERENCES MyExcelLinkedTable (ID)
;

gives an error, 'Operation not supported on linked tables'.

I even tried 'something different':

ALTER TABLE MyNewTable
ADD CONSTRAINT ck__excel_link
CHECK
(
EXISTS
(
SELECT *
FROM MyExcelLinkedTable
WHERE MyKeyCol=ID
)
)
;

Not correct syntax I know, but revealingly I got an error, 'The object
(MyExcelLinkedTable) cannot be used in a CHECK constraint clause.'

Jamie.

--

 

[Terry]

Hi John,
I had thought of this as an alternative way to do things, thanks.

Regards

 

[Terry]

Hi Jamie,
I'll try and use this on one of my tables, thanks.
Regards