SCHEMA - primary key - SQL problem

29 Dec 2005 6:03 PM

Jeff User

Hi
I posted this in another group and got no reply.
Hope someone here can help. Perhaps this is more of a SQL programming
issue than a schema or PK issue, as the actual problem is duplicate
records:

I have reviewed previous posts about finding primary key, table schema
information, etc...
I have got quite far but am now getting duplicate records. Not sure
what else to add here to fix this.
If I use this query, I get various column information and the Primary
key is identified by my PK_Col column correctly:

SELECT cols.COLUMN_NAME,
   DATA_TYPE,
   Length = case
          when CHARACTER_MAXIMUM_LENGTH is null
    then st.length
          else CHARACTER_MAXIMUM_LENGTH END,
   CHARACTER_MAXIMUM_LENGTH,
   PK_COL = case when K.COLUMN_NAME = cols.COLUMN_NAME
         then 'PK' else '' END,
   T.CONSTRAINT_NAME,
   IS_NULLABLE
FROM (Test.INFORMATION_SCHEMA.COLUMNS cols
   left join systypes st on cols.DATA_TYPE = st.name)
    left join
    (INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
    INNER JOIN
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
        ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME)
     on T.TABLE_NAME = cols.TABLE_NAME

WHERE cols.TABLE_NAME = 'Tester'
and T.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY cols.COLUMN_NAME

The table "tester" has one PK (first column) and one foreign key (2nd
column). The above query returns one row for each column in table
tester. So this is correct results:

COL_1     varchar   26      26      PK   PK_Tester     NO
COL_2     Int              4         4                PK_Tester     NO
etc...
(where PK_Tester is the name of the Primary Key constraint)
Now I also would like to see if a column is a foreign key. However,
when I change the WHERE clause like this :
WHERE cols.TABLE_NAME = 'Tester'
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
         or T.CONSTRAINT_TYPE = 'FOREIGN KEY')

I now get 2 records for every table column represented. The name of
the PK and the name of the FK are each displayed for every table
column row of data I get back;
For expl: This is what I should get:
COL_1    varchar     26    26    PK   PK_Tester   NO
COL_2     Int              4       4      PK   FK_Tester    NO
etc...
(where PK_Tester is the name of the Primary Key constraint
and FK_Tester is the name of the Fioreign Key constraint)

But this is what I am getting:
COL_1   varchar   26    26    PK   PK_Tester   NO
COL_1   varchar   26    26             FK_Tester   NO
COL_2     Int           4       4       PK   FK_Tester   NO
COL_2     Int           4       4               PK_Tester   NO

Note, I didnt set up code to display FK yet, in the PK_Col column.
That will come after I resolve the duplicate result rows problem.

Do I need to add more criteria to a JOIN clause or a WHERE clause and
if so, any idea what?

Thanks in advance
Jeff

29 Dec 2005 6:35 PM

Trey Walpole

so you're trying to get the column list and indicate which column(s) are
int the PK and FKs?

change your FROM and WHERE to be

the dupes and repeating PKs are fixed by these changes
* have to move the constraint_type from the where to the left join
[otherwise, it will act like an inner join]
* have to join the key_column_usage view on the column as well
[otherwise, you get all columns showing as the PK]

FROM (INFORMATION_SCHEMA.COLUMNS cols
    left join systypes st on cols.DATA_TYPE = st.name)
     left join
    (INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
     INNER JOIN
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
         ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME)
      on T.TABLE_NAME = cols.TABLE_NAME
       and k.column_name = cols.column_name
       and T.CONSTRAINT_TYPE = 'PRIMARY KEY'

WHERE cols.TABLE_NAME = 'Tester'

Jeff User wrote:
Show quote

> Hi
> I posted this in another group and got no reply.
> Hope someone here can help. Perhaps this is more of a SQL programming
> issue than a schema or PK issue, as the actual problem is duplicate
> records:
>
> I have reviewed previous posts about finding primary key, table schema
> information, etc...
> I have got quite far but am now getting duplicate records. Not sure
> what else to add here to fix this.
> If I use this query, I get various column information and the Primary
> key is identified by my PK_Col column correctly:
>
> SELECT cols.COLUMN_NAME,
>    DATA_TYPE,
>    Length = case
>           when CHARACTER_MAXIMUM_LENGTH is null
>     then st.length
>           else CHARACTER_MAXIMUM_LENGTH END,
>    CHARACTER_MAXIMUM_LENGTH,
>    PK_COL = case when K.COLUMN_NAME = cols.COLUMN_NAME
>          then 'PK' else '' END,
>    T.CONSTRAINT_NAME,
>    IS_NULLABLE
> FROM (Test.INFORMATION_SCHEMA.COLUMNS cols
>    left join systypes st on cols.DATA_TYPE = st.name)
>     left join
>     (INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
>     INNER JOIN
>         INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
>         ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME)
>      on T.TABLE_NAME = cols.TABLE_NAME
>
> WHERE cols.TABLE_NAME = 'Tester'
> and T.CONSTRAINT_TYPE = 'PRIMARY KEY'
> ORDER BY cols.COLUMN_NAME
>
> The table "tester" has one PK (first column) and one foreign key (2nd
> column). The above query returns one row for each column in table
> tester. So this is correct results:
>
> COL_1     varchar   26      26      PK   PK_Tester     NO
> COL_2     Int              4         4                PK_Tester     NO
> etc...
> (where PK_Tester is the name of the Primary Key constraint)
> Now I also would like to see if a column is a foreign key. However,
> when I change the WHERE clause like this :
> WHERE cols.TABLE_NAME = 'Tester'
> and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
>          or T.CONSTRAINT_TYPE = 'FOREIGN KEY')
>
> I now get 2 records for every table column represented. The name of
> the PK and the name of the FK are each displayed for every table
> column row of data I get back;
> For expl: This is what I should get:
> COL_1    varchar     26    26    PK   PK_Tester   NO
> COL_2     Int              4       4      PK   FK_Tester    NO
> etc...
> (where PK_Tester is the name of the Primary Key constraint
> and FK_Tester is the name of the Fioreign Key constraint)
>
>
> But this is what I am getting:
> COL_1   varchar   26    26    PK   PK_Tester   NO
> COL_1   varchar   26    26             FK_Tester   NO
> COL_2     Int           4       4       PK   FK_Tester   NO
> COL_2     Int           4       4               PK_Tester   NO
>
> Note, I didnt set up code to display FK yet, in the PK_Col column.
> That will come after I resolve the duplicate result rows problem.
>
> Do I need to add more criteria to a JOIN clause or a WHERE clause and
> if so, any idea what?
>
> Thanks in advance
> Jeff

29 Dec 2005 8:02 PM

Jeff User

Thanks again
Works great !!
Jeff

On Thu, 29 Dec 2005 12:35:20 -0600, Trey Walpole
<treypole@newsgroups.nospam> wrote:

Show quote

>so you're trying to get the column list and indicate which column(s) are
>int the PK and FKs?
>
>change your FROM and WHERE to be
>
>the dupes and repeating PKs are fixed by these changes
>* have to move the constraint_type from the where to the left join
>[otherwise, it will act like an inner join]
>* have to join the key_column_usage view on the column as well
>[otherwise, you get all columns showing as the PK]
>
>
>
>FROM (INFORMATION_SCHEMA.COLUMNS cols
>    left join systypes st on cols.DATA_TYPE = st.name)
>     left join
>    (INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
>     INNER JOIN
>         INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
>         ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME)
>      on T.TABLE_NAME = cols.TABLE_NAME
>       and k.column_name = cols.column_name
>       and T.CONSTRAINT_TYPE = 'PRIMARY KEY'
>
>WHERE cols.TABLE_NAME = 'Tester'
>
>
>
>Jeff User wrote:
>> Hi
>> I posted this in another group and got no reply.
>> Hope someone here can help. Perhaps this is more of a SQL programming
>> issue than a schema or PK issue, as the actual problem is duplicate
>> records:
>>
>> I have reviewed previous posts about finding primary key, table schema
>> information, etc...
>> I have got quite far but am now getting duplicate records. Not sure
>> what else to add here to fix this.
>> If I use this query, I get various column information and the Primary
>> key is identified by my PK_Col column correctly:
>>
>> SELECT cols.COLUMN_NAME,
>>    DATA_TYPE,
>>    Length = case
>>           when CHARACTER_MAXIMUM_LENGTH is null
>>     then st.length
>>           else CHARACTER_MAXIMUM_LENGTH END,
>>    CHARACTER_MAXIMUM_LENGTH,
>>    PK_COL = case when K.COLUMN_NAME = cols.COLUMN_NAME
>>          then 'PK' else '' END,
>>    T.CONSTRAINT_NAME,
>>    IS_NULLABLE
>> FROM (Test.INFORMATION_SCHEMA.COLUMNS cols
>>    left join systypes st on cols.DATA_TYPE = st.name)
>>     left join
>>     (INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
>>     INNER JOIN
>>         INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
>>         ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME)
>>      on T.TABLE_NAME = cols.TABLE_NAME
>>
>> WHERE cols.TABLE_NAME = 'Tester'
>> and T.CONSTRAINT_TYPE = 'PRIMARY KEY'
>> ORDER BY cols.COLUMN_NAME
>>
>> The table "tester" has one PK (first column) and one foreign key (2nd
>> column). The above query returns one row for each column in table
>> tester. So this is correct results:
>>
>> COL_1     varchar   26      26      PK   PK_Tester     NO
>> COL_2     Int              4         4                PK_Tester     NO
>> etc...
>> (where PK_Tester is the name of the Primary Key constraint)
>> Now I also would like to see if a column is a foreign key. However,
>> when I change the WHERE clause like this :
>> WHERE cols.TABLE_NAME = 'Tester'
>> and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
>>          or T.CONSTRAINT_TYPE = 'FOREIGN KEY')
>>
>> I now get 2 records for every table column represented. The name of
>> the PK and the name of the FK are each displayed for every table
>> column row of data I get back;
>> For expl: This is what I should get:
>> COL_1    varchar     26    26    PK   PK_Tester   NO
>> COL_2     Int              4       4      PK   FK_Tester    NO
>> etc...
>> (where PK_Tester is the name of the Primary Key constraint
>> and FK_Tester is the name of the Fioreign Key constraint)
>>
>>
>> But this is what I am getting:
>> COL_1   varchar   26    26    PK   PK_Tester   NO
>> COL_1   varchar   26    26             FK_Tester   NO
>> COL_2     Int           4       4       PK   FK_Tester   NO
>> COL_2     Int           4       4               PK_Tester   NO
>>
>> Note, I didnt set up code to display FK yet, in the PK_Col column.
>> That will come after I resolve the duplicate result rows problem.
>>
>> Do I need to add more criteria to a JOIN clause or a WHERE clause and
>> if so, any idea what?
>>
>> Thanks in advance
>> Jeff

30 Dec 2005 6:52 AM

Razvan Socol

Hi, Jeff

First, we must consider that:
1. A primary key (or a foreign key) may consist of more than one
column.
2. A column may be part of the primary key AND a foreign key.
3. A column may be part of more than one foreign key.
4. Unique keys have almost the same importance as the primary key.

In case (2) or (3) happens, we need to define if:
a) all the PK/FK names are displayed (therefore causing duplicates in
the column names)
b) only one PK/FK name is displayed for each column (the PK or the
first of the FK-s).

In the mean time, I would use the following query (which doesn't
display any PK/FK name), to see if each column is part of the PK or is
part of a FK:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
    NUMERIC_PRECISION, NUMERIC_SCALE,
    CASE WHEN EXISTS (
        SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        ON kcu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
        AND kcu.CONSTRAINT_SCHEMA=tc.CONSTRAINT_SCHEMA
        WHERE tc.TABLE_NAME=cols.TABLE_NAME
        AND tc.TABLE_SCHEMA=cols.TABLE_SCHEMA
        AND kcu.COLUMN_NAME=cols.COLUMN_NAME
        AND tc.CONSTRAINT_TYPE='PRIMARY KEY'
    ) THEN 'PK' ELSE '' END AS PK,
    CASE WHEN EXISTS (
        SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        ON kcu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
        AND kcu.CONSTRAINT_SCHEMA=tc.CONSTRAINT_SCHEMA
        WHERE tc.TABLE_NAME=cols.TABLE_NAME
        AND tc.TABLE_SCHEMA=cols.TABLE_SCHEMA
        AND kcu.COLUMN_NAME=cols.COLUMN_NAME
        AND tc.CONSTRAINT_TYPE='FOREIGN KEY'
    ) THEN 'FK' ELSE '' END AS FK
FROM INFORMATION_SCHEMA.COLUMNS cols
WHERE TABLE_NAME='YourTableName'
ORDER BY ORDINAL_POSITION

Also, it is not clear to me what is the length/size for each column
that you want to be displayed. If it's CHARACTER_MAXIMUM_LENGTH, then
it won't be the actual size (in bytes), because nvarchar/nchar are
stored using 2 bytes/character.

Razvan